SELECT
  id,
  COUNT(DISTINCT CASE WHEN passed THEN task_id END) || '/' || COUNT(DISTINCT task_id) AS "Tasks passed",
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN passed THEN task_id END) / NULLIF(COUNT(DISTINCT task_id), 0), 1) AS "Pass Rate"
FROM (
  SELECT
    CAST(results.participants.agent AS VARCHAR) AS id,
    row.task_id,
    row.passed
  FROM results
  CROSS JOIN UNNEST(results.results) AS outer_rows(outer_row)
  CROSS JOIN UNNEST(outer_row.results) AS nested_rows(row)
  WHERE results.status = 'completed' AND results.participants.agent IS NOT NULL
  UNION ALL
  SELECT
    CAST(results.participants.agent AS VARCHAR) AS id,
    outer_row.task_id,
    outer_row.passed
  FROM results
  CROSS JOIN UNNEST(results.results) AS outer_rows(outer_row)
  WHERE results.status = 'completed' AND results.participants.agent IS NOT NULL AND outer_row.task_id IS NOT NULL
) AS flat
GROUP BY id
ORDER BY "Pass Rate" DESC NULLS LAST

SELECT
  id,
  category AS "Category",
  COUNT(DISTINCT CASE WHEN passed THEN task_id END) || '/' || COUNT(DISTINCT task_id) AS "Tasks passed",
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN passed THEN task_id END) / NULLIF(COUNT(DISTINCT task_id), 0), 1) AS "Pass Rate"
FROM (
  SELECT
    CAST(results.participants.agent AS VARCHAR) AS id,
    row.category,
    row.task_id,
    row.passed
  FROM results
  CROSS JOIN UNNEST(results.results) AS outer_rows(outer_row)
  CROSS JOIN UNNEST(outer_row.results) AS nested_rows(row)
  WHERE results.status = 'completed' AND results.participants.agent IS NOT NULL
  UNION ALL
  SELECT
    CAST(results.participants.agent AS VARCHAR) AS id,
    outer_row.category,
    outer_row.task_id,
    outer_row.passed
  FROM results
  CROSS JOIN UNNEST(results.results) AS outer_rows(outer_row)
  WHERE results.status = 'completed' AND results.participants.agent IS NOT NULL AND outer_row.task_id IS NOT NULL
) AS flat
WHERE category IS NOT NULL
GROUP BY id, category
ORDER BY id, category

SELECT
  id,
  difficulty AS "Difficulty",
  COUNT(DISTINCT CASE WHEN passed THEN task_id END) || '/' || COUNT(DISTINCT task_id) AS "Tasks passed",
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN passed THEN task_id END) / NULLIF(COUNT(DISTINCT task_id), 0), 1) AS "Pass Rate"
FROM (
  SELECT
    CAST(results.participants.agent AS VARCHAR) AS id,
    row.difficulty,
    row.task_id,
    row.passed
  FROM results
  CROSS JOIN UNNEST(results.results) AS outer_rows(outer_row)
  CROSS JOIN UNNEST(outer_row.results) AS nested_rows(row)
  WHERE results.status = 'completed' AND results.participants.agent IS NOT NULL
  UNION ALL
  SELECT
    CAST(results.participants.agent AS VARCHAR) AS id,
    outer_row.difficulty,
    outer_row.task_id,
    outer_row.passed
  FROM results
  CROSS JOIN UNNEST(results.results) AS outer_rows(outer_row)
  WHERE results.status = 'completed' AND results.participants.agent IS NOT NULL AND outer_row.task_id IS NOT NULL
) AS flat
WHERE difficulty IS NOT NULL
GROUP BY id, difficulty
ORDER BY id, difficulty