SELECT id, ROUND(AVG(won::INT), 3) AS win_rate, ROUND(AVG(CASE WHEN role = 'spy' THEN won::INT END), 3) AS spy_win_rate, ROUND(AVG(CASE WHEN role = 'non-spy' THEN won::INT END), 3) AS non_spy_win_rate, COUNT(*) AS total_games_played FROM (SELECT json_extract_string(CAST(root.participants AS JSON), CONCAT('$."', p.player.name, '"')) AS id, p.player.role AS role, p.player.won AS won FROM results AS root CROSS JOIN UNNEST(root.results) AS g(game) CROSS JOIN UNNEST(g.game.players) AS p(player) WHERE json_extract_string(CAST(root.participants AS JSON), CONCAT('$."', p.player.name, '"')) IS NOT NULL) GROUP BY id