SELECT id, category, ROUND(accuracy * 100, 1) AS "Accuracy %", ROUND(avg_score, 2) AS "Avg Score", total_tests AS "Tests" FROM (SELECT results.participants.agent AS id, res.category AS category, AVG(CASE WHEN res.correct THEN 1.0 ELSE 0.0 END) AS accuracy, AVG(res.score) AS avg_score, COUNT(*) AS total_tests FROM results, UNNEST(results.results) AS t(res_item), UNNEST([res_item.details]) AS d(details), UNNEST(details.results) AS r(res) GROUP BY id, category) ORDER BY category, accuracy DESC;

SELECT id, ROUND(pass_rate * 100, 1) AS "Accuracy %", ROUND(avg_score, 2) AS "Avg Score", ROUND(avg_time, 1) AS "Avg Time (s)", total_tests AS "Tests" FROM (SELECT results.participants.agent AS id, AVG(CASE WHEN res.correct THEN 1.0 ELSE 0.0 END) AS pass_rate, AVG(res.score) AS avg_score, AVG(res.time_taken) AS avg_time, COUNT(*) AS total_tests FROM results, UNNEST(results.results) AS t(res_item), UNNEST([res_item.details]) AS d(details), UNNEST(details.results) AS r(res) GROUP BY id) ORDER BY pass_rate DESC, avg_score DESC;