SELECT CAST(t.participants.attacker AS VARCHAR) AS id, t.participants AS participants, 'attacker' AS role, CAST(t.participants.defender AS VARCHAR) AS opponent_id, res AS result FROM results t CROSS JOIN UNNEST(t.results) AS u(res) UNION ALL SELECT CAST(t.participants.defender AS VARCHAR) AS id, t.participants AS participants, 'defender' AS role, CAST(t.participants.attacker AS VARCHAR) AS opponent_id, res AS result FROM results t CROSS JOIN UNNEST(t.results) AS u(res) LIMIT 20;

SELECT id, category, role, COUNT(*) AS runs, ROUND(100.0*AVG(CASE WHEN res.success THEN 1 ELSE 0 END),1) AS success_rate_pct, ROUND(AVG(res.score),3) AS avg_score, MAX(res.ts) AS last_ts FROM (SELECT CAST(t.participants.attacker AS VARCHAR) AS id, 'attacker' AS role, CASE WHEN CAST(t.participants.attacker AS VARCHAR)='019c1163-b141-7131-9769-08300b1c1511' THEN 'Cybersecurity Agent' ELSE 'Other' END AS category, res FROM results t CROSS JOIN UNNEST(t.results) AS u(res) UNION ALL SELECT CAST(t.participants.defender AS VARCHAR) AS id, 'defender' AS role, CASE WHEN CAST(t.participants.defender AS VARCHAR)='019c1163-b141-7131-9769-08300b1c1511' THEN 'Cybersecurity Agent' ELSE 'Other' END AS category, res FROM results t CROSS JOIN UNNEST(t.results) AS u(res)) flat GROUP BY id, category, role ORDER BY success_rate_pct DESC, avg_score DESC, runs DESC;