SELECT
  id,
  COUNT(*) AS Runs,
  SUM(score) AS Score,
  SUM(total) AS Total,
  ROUND(SUM(score) * 1.0 / NULLIF(SUM(total), 0), 4) AS Weighted_Accuracy,
  ROUND(AVG(avg_time), 4) AS Avg_Time,
  SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END) AS Completed,
  SUM(CASE WHEN status!='completed' OR status IS NULL THEN 1 ELSE 0 END) AS Not_Completed
FROM (
  SELECT
    t.participants.agent AS id,
    TRY_CAST(r.result.score AS DOUBLE) AS score,
    TRY_CAST(r.result.total AS DOUBLE) AS total,
    TRY_CAST(r.result.avg_time AS DOUBLE) AS avg_time,
    r.result.status AS status
  FROM results t
  CROSS JOIN UNNEST(t.results) AS r(result)
)
GROUP BY id
ORDER BY Weighted_Accuracy DESC, Score DESC, Runs DESC, id;

SELECT
  id,
  level AS Level,
  COUNT(*) AS Runs,
  SUM(score) AS Score,
  SUM(total) AS Total,
  ROUND(SUM(score) * 1.0 / NULLIF(SUM(total), 0), 4) AS Weighted_Accuracy,
  ROUND(AVG(avg_time), 4) AS Avg_Time
FROM (
  SELECT
    t.participants.agent AS id,
    TRY_CAST(r.result.level AS INTEGER) AS level,
    TRY_CAST(r.result.score AS DOUBLE) AS score,
    TRY_CAST(r.result.total AS DOUBLE) AS total,
    TRY_CAST(r.result.avg_time AS DOUBLE) AS avg_time
  FROM results t
  CROSS JOIN UNNEST(t.results) AS r(result)
)
GROUP BY id, level
ORDER BY Level ASC, Weighted_Accuracy DESC, Score DESC, id;

SELECT
  id,
  agent_name AS Agent_Name,
  ts AS Timestamp,
  level AS Level,
  task_ids AS Task_IDs,
  accuracy AS Accuracy,
  score AS Score,
  total AS Total,
  avg_time AS Avg_Time,
  status AS Status,
  note AS Note
FROM (
  SELECT
    t.participants.agent AS id,
    r.result.agent_name AS agent_name,
    r.result.timestamp AS ts,
    TRY_CAST(r.result.level AS INTEGER) AS level,
    r.result.task_ids AS task_ids,
    TRY_CAST(r.result.accuracy AS DOUBLE) AS accuracy,
    TRY_CAST(r.result.score AS DOUBLE) AS score,
    TRY_CAST(r.result.total AS DOUBLE) AS total,
    TRY_CAST(r.result.avg_time AS DOUBLE) AS avg_time,
    r.result.status AS status,
    r.result.note AS note,
    ROW_NUMBER() OVER (
      PARTITION BY t.participants.agent
      ORDER BY r.result.timestamp DESC NULLS LAST
    ) AS rn
  FROM results t
  CROSS JOIN UNNEST(t.results) AS r(result)
)
WHERE rn = 1
ORDER BY id;