confused-labels.sql

WITH least_margin AS (
  SELECT 
    image_id, image, detection,
    detection.scores[0] - detection.scores[1] as margin FROM (
      SELECT 
        image_id, 
        image, 
        explode(ML_PREDICT(class_scores, image)) AS detection 
      FROM coco
  ) ORDER BY margin LIMIT 1000
)

SELECT 
  coco_name(detection.label_ids[0]) as first_class,
  coco_name(detection.label_ids[1]) as second_class,
  count(image_id) as cnt,
  avg(margin) as avg_margin
FROM least_margin
GROUP BY detection.label_ids[0], detection.label_ids[1]
SORT BY cnt DESC