SELECT t.participants.opencaptcha_solver AS id, ROUND(AVG(r.result.detail.overall_accuracy), 2) AS "Accuracy (%)", ROUND(AVG(r.result.detail.average_solve_time), 2) AS "Avg Time (s)", SUM(r.result.detail.correct_predictions) AS "Solved", SUM(r.result.detail.total_attempts) AS "Total", COUNT(*) AS "Runs" FROM results AS t CROSS JOIN UNNEST(t.results) AS r(result) GROUP BY t.participants.opencaptcha_solver ORDER BY AVG(r.result.detail.overall_accuracy) DESC, id;

SELECT t.participants.opencaptcha_solver AS id, tm.type_metric.puzzle_type AS "Puzzle Type", ROUND(AVG(tm.type_metric.accuracy), 2) AS "Accuracy (%)", ROUND(AVG(tm.type_metric.average_solve_time), 2) AS "Avg Time (s)", SUM(tm.type_metric.correct_predictions) AS "Solved", SUM(tm.type_metric.total_attempts) AS "Total" FROM results AS t CROSS JOIN UNNEST(t.results) AS r(result) CROSS JOIN UNNEST(r.result.detail.type_metrics) AS tm(type_metric) GROUP BY t.participants.opencaptcha_solver, tm.type_metric.puzzle_type ORDER BY tm.type_metric.puzzle_type, AVG(tm.type_metric.accuracy) DESC, id;