SELECT id, 'https://frontend_url.dev/?agentId=' || id AS "Agent Full Traceability Url", 1000 + SUM(COALESCE(elo_delta, 0)) AS "ELO", COUNT(*) AS "Games", SUM(CASE WHEN won THEN 1 ELSE 0 END) AS "Wins", ROUND(SUM(CASE WHEN won THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS "Win %", ROUND(AVG(aggregate) * 100, 1) || '%' AS "Avg Aggregate", ROUND(AVG(influence) * 100, 1) || '%' AS "Avg Influence", ROUND(AVG(consistency) * 100, 1) || '%' AS "Avg Consistency", ROUND(AVG(sabotage) * 100, 1) || '%' AS "Avg Sabotage", ROUND(AVG(detection) * 100, 1) || '%' AS "Avg Detection", ROUND(AVG(deception) * 100, 1) || '%' AS "Avg Deception" FROM (SELECT CASE s.unnest.player_name WHEN 'Player_1' THEN results.participants.Player_1 WHEN 'Player_2' THEN results.participants.Player_2 WHEN 'Player_3' THEN results.participants.Player_3 WHEN 'Player_4' THEN results.participants.Player_4 WHEN 'Player_5' THEN results.participants.Player_5 WHEN 'Player_6' THEN results.participants.Player_6 WHEN 'Player_7' THEN results.participants.Player_7 WHEN 'Player_8' THEN results.participants.Player_8 END AS id, s.unnest.won AS won, s.unnest.elo_delta AS elo_delta, s.unnest.metrics.aggregate_score AS aggregate, s.unnest.metrics.influence_score AS influence, s.unnest.metrics.consistency_score AS consistency, s.unnest.metrics.sabotage_score AS sabotage, s.unnest.metrics.detection_score AS detection, s.unnest.metrics.deception_score AS deception FROM results CROSS JOIN UNNEST(results.results) AS r(unnest) CROSS JOIN UNNEST(r.unnest.scores) AS s(unnest)) GROUP BY id ORDER BY "ELO" DESC

SELECT id, COUNT(*) AS "Games", SUM(CASE WHEN won THEN 1 ELSE 0 END) AS "Wins", ROUND(SUM(CASE WHEN won THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS "Win %", ROUND(AVG(aggregate) * 100, 1) || '%' AS "Avg Aggregate", ROUND(AVG(influence) * 100, 1) || '%' AS "Avg Influence", ROUND(AVG(consistency) * 100, 1) || '%' AS "Avg Consistency", ROUND(AVG(sabotage) * 100, 1) || '%' AS "Avg Sabotage", ROUND(AVG(detection) * 100, 1) || '%' AS "Avg Detection", ROUND(AVG(deception) * 100, 1) || '%' AS "Avg Deception" FROM (SELECT CASE s.unnest.player_name WHEN 'Player_1' THEN results.participants.Player_1 WHEN 'Player_2' THEN results.participants.Player_2 WHEN 'Player_3' THEN results.participants.Player_3 WHEN 'Player_4' THEN results.participants.Player_4 WHEN 'Player_5' THEN results.participants.Player_5 WHEN 'Player_6' THEN results.participants.Player_6 WHEN 'Player_7' THEN results.participants.Player_7 WHEN 'Player_8' THEN results.participants.Player_8 END AS id, s.unnest.won AS won, s.unnest.metrics.aggregate_score AS aggregate, s.unnest.metrics.influence_score AS influence, s.unnest.metrics.consistency_score AS consistency, s.unnest.metrics.sabotage_score AS sabotage, s.unnest.metrics.detection_score AS detection, s.unnest.metrics.deception_score AS deception FROM results CROSS JOIN UNNEST(results.results) AS r(unnest) CROSS JOIN UNNEST(r.unnest.scores) AS s(unnest) WHERE s.unnest.role = 'werewolf') GROUP BY id HAVING COUNT(*) > 0 ORDER BY "Win %" DESC

SELECT id, COUNT(*) AS "Games", SUM(CASE WHEN won THEN 1 ELSE 0 END) AS "Wins", ROUND(SUM(CASE WHEN won THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS "Win %", ROUND(AVG(aggregate) * 100, 1) || '%' AS "Avg Aggregate", ROUND(AVG(influence) * 100, 1) || '%' AS "Avg Influence", ROUND(AVG(consistency) * 100, 1) || '%' AS "Avg Consistency", ROUND(AVG(sabotage) * 100, 1) || '%' AS "Avg Sabotage", ROUND(AVG(detection) * 100, 1) || '%' AS "Avg Detection", ROUND(AVG(deception) * 100, 1) || '%' AS "Avg Deception" FROM (SELECT CASE s.unnest.player_name WHEN 'Player_1' THEN results.participants.Player_1 WHEN 'Player_2' THEN results.participants.Player_2 WHEN 'Player_3' THEN results.participants.Player_3 WHEN 'Player_4' THEN results.participants.Player_4 WHEN 'Player_5' THEN results.participants.Player_5 WHEN 'Player_6' THEN results.participants.Player_6 WHEN 'Player_7' THEN results.participants.Player_7 WHEN 'Player_8' THEN results.participants.Player_8 END AS id, s.unnest.won AS won, s.unnest.metrics.aggregate_score AS aggregate, s.unnest.metrics.influence_score AS influence, s.unnest.metrics.consistency_score AS consistency, s.unnest.metrics.sabotage_score AS sabotage, s.unnest.metrics.detection_score AS detection, s.unnest.metrics.deception_score AS deception FROM results CROSS JOIN UNNEST(results.results) AS r(unnest) CROSS JOIN UNNEST(r.unnest.scores) AS s(unnest) WHERE s.unnest.team = 'villagers') GROUP BY id HAVING COUNT(*) > 0 ORDER BY "Win %" DESC

SELECT id, 'https://frontend_url.dev/?run=' || REPLACE(game_file, '.json', '') AS "Game URL", role AS "Role", CASE WHEN won THEN 'Won' ELSE 'Lost' END AS "Result", COALESCE(elo_delta, 0) AS "ELO +/-", ROUND(aggregate * 100, 1) || '%' AS "Aggregate Score", ROUND(influence * 100, 1) || '%' AS "Influence", ROUND(consistency * 100, 1) || '%' AS "Consistency", ROUND(sabotage * 100, 1) || '%' AS "Sabotage", ROUND(detection * 100, 1) || '%' AS "Detection", ROUND(deception * 100, 1) || '%' AS "Deception" FROM (SELECT CASE s.unnest.player_name WHEN 'Player_1' THEN results.participants.Player_1 WHEN 'Player_2' THEN results.participants.Player_2 WHEN 'Player_3' THEN results.participants.Player_3 WHEN 'Player_4' THEN results.participants.Player_4 WHEN 'Player_5' THEN results.participants.Player_5 WHEN 'Player_6' THEN results.participants.Player_6 WHEN 'Player_7' THEN results.participants.Player_7 WHEN 'Player_8' THEN results.participants.Player_8 END AS id, results.filename AS game_file, s.unnest.role AS role, s.unnest.won AS won, s.unnest.elo_delta AS elo_delta, s.unnest.metrics.aggregate_score AS aggregate, s.unnest.metrics.influence_score AS influence, s.unnest.metrics.consistency_score AS consistency, s.unnest.metrics.sabotage_score AS sabotage, s.unnest.metrics.detection_score AS detection, s.unnest.metrics.deception_score AS deception FROM results CROSS JOIN UNNEST(results.results) AS r(unnest) CROSS JOIN UNNEST(r.unnest.scores) AS s(unnest)) ORDER BY game_file DESC