SELECT id, ROUND(accuracy, 3) AS "Accuracy", correct_tasks AS "Correct", total_tasks AS "Total", run_ts AS "Date" FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY accuracy DESC, run_ts DESC) AS rn FROM (SELECT t.participants.medical_agent AS id, COALESCE(r.result.accuracy, r.result.success_rate) AS accuracy, r.result.correct_tasks AS correct_tasks, r.result.total_tasks AS total_tasks, MAX(tr.task.detail.timestamp) AS run_ts FROM results AS t CROSS JOIN UNNEST(t.results) AS r(result) CROSS JOIN UNNEST(r.result.task_results) AS tr(task) WHERE r.result.subtask = 'subtask1' GROUP BY 1,2,3,4) runs) ranked WHERE rn = 1 ORDER BY accuracy DESC, run_ts DESC;

SELECT id, ROUND(accuracy, 3) AS "Accuracy", ROUND(hallucination_rate, 3) AS "Hallucination Rate", total_tasks AS "Cases", run_ts AS "Date" FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY accuracy DESC, hallucination_rate ASC NULLS LAST, run_ts DESC) AS rn FROM (SELECT t.participants.medical_agent AS id, COALESCE(r.result.accuracy, r.result.success_rate) AS accuracy, r.result.hallucination_rate AS hallucination_rate, r.result.total_tasks AS total_tasks, MAX(tr.task.detail.timestamp) AS run_ts FROM results AS t CROSS JOIN UNNEST(t.results) AS r(result) CROSS JOIN UNNEST(r.result.task_results) AS tr(task) WHERE r.result.subtask = 'subtask2' GROUP BY 1,2,3,4) runs) ranked WHERE rn = 1 ORDER BY accuracy DESC, hallucination_rate ASC NULLS LAST, run_ts DESC;

SELECT id, ROUND(AVG(accuracy), 3) AS "Avg Accuracy", COUNT(*) AS "Submissions", MAX(run_ts) AS "Latest Date" FROM (SELECT t.participants.medical_agent AS id, COALESCE(r.result.accuracy, r.result.success_rate) AS accuracy, MAX(tr.task.detail.timestamp) AS run_ts FROM results AS t CROSS JOIN UNNEST(t.results) AS r(result) CROSS JOIN UNNEST(r.result.task_results) AS tr(task) GROUP BY 1,2) per_result GROUP BY id ORDER BY "Avg Accuracy" DESC, "Latest Date" DESC;