SELECT
  id,
  COUNT(*) AS "Games Played",
  ROUND(AVG(CASE WHEN team_win THEN 1.0 ELSE 0.0 END) * 100.0, 1) AS "Winrate",
  ROUND(
    AVG(voting_accuracy) FILTER (WHERE role <> 'Werewolf' AND voting_accuracy IS NOT NULL) * 100.0,
    1
  ) AS "Voting Acc %"
FROM (
  SELECT
    json_extract_string(CAST(root.participants AS JSON), CONCAT('$."', k.player_name, '"')) AS id,
    json_extract_string(rep_json, '$.role') AS role,
    CAST(json_extract(rep_json, '$.team_win') AS BOOLEAN) AS team_win,
    TRY_CAST(json_extract_string(rep_json, '$.voting_accuracy') AS DOUBLE) AS voting_accuracy
  FROM results AS root
  CROSS JOIN UNNEST(root.results) AS g(game)
  CROSS JOIN UNNEST(json_keys(CAST(g.game.reports AS JSON))) AS k(player_name)
  CROSS JOIN LATERAL (
    SELECT json_extract(CAST(g.game.reports AS JSON), CONCAT('$."', k.player_name, '"')) AS rep_json
  )
  WHERE json_extract_string(CAST(root.participants AS JSON), CONCAT('$."', k.player_name, '"')) IS NOT NULL
) t
GROUP BY id
ORDER BY "Winrate" DESC, "Voting Acc %" DESC, id

SELECT
  id,
  COUNT(*) AS "Games Played",
  ROUND(AVG(CASE WHEN team_win THEN 1.0 ELSE 0.0 END) * 100.0, 1) AS "Winrate"
FROM (
  SELECT
    json_extract_string(CAST(r.participants AS JSON), CONCAT('$."', k.player_name, '"')) AS id,
    json_extract_string(rep_json, '$.role') AS role,
    CAST(json_extract(rep_json, '$.team_win') AS BOOLEAN) AS team_win
  FROM results r
  CROSS JOIN UNNEST(r.results) AS g(game)
  CROSS JOIN UNNEST(json_keys(CAST(g.game.reports AS JSON))) AS k(player_name)
  CROSS JOIN LATERAL (
    SELECT json_extract(CAST(g.game.reports AS JSON), CONCAT('$."', k.player_name, '"')) AS rep_json
  )
  WHERE json_extract_string(CAST(r.participants AS JSON), CONCAT('$."', k.player_name, '"')) IS NOT NULL
) t
WHERE role = 'Werewolf'
GROUP BY id
ORDER BY "Winrate" DESC, "Games Played" DESC, id

SELECT
  id,
  COUNT(*) AS "Games Played",
  ROUND(AVG(CASE WHEN team_win THEN 1.0 ELSE 0.0 END) * 100.0, 1) AS "Winrate"
FROM (
  SELECT
    json_extract_string(CAST(r.participants AS JSON), CONCAT('$."', k.player_name, '"')) AS id,
    json_extract_string(rep_json, '$.role') AS role,
    CAST(json_extract(rep_json, '$.team_win') AS BOOLEAN) AS team_win
  FROM results r
  CROSS JOIN UNNEST(r.results) AS g(game)
  CROSS JOIN UNNEST(json_keys(CAST(g.game.reports AS JSON))) AS k(player_name)
  CROSS JOIN LATERAL (
    SELECT json_extract(CAST(g.game.reports AS JSON), CONCAT('$."', k.player_name, '"')) AS rep_json
  )
  WHERE json_extract_string(CAST(r.participants AS JSON), CONCAT('$."', k.player_name, '"')) IS NOT NULL
) t
WHERE role = 'Villager'
GROUP BY id
ORDER BY "Winrate" DESC, "Games Played" DESC, id

SELECT
  id,
  COUNT(*) AS "Games Played",
  ROUND(AVG(CASE WHEN team_win THEN 1.0 ELSE 0.0 END) * 100.0, 1) AS "Winrate"
FROM (
  SELECT
    json_extract_string(CAST(r.participants AS JSON), CONCAT('$."', k.player_name, '"')) AS id,
    json_extract_string(rep_json, '$.role') AS role,
    CAST(json_extract(rep_json, '$.team_win') AS BOOLEAN) AS team_win
  FROM results r
  CROSS JOIN UNNEST(r.results) AS g(game)
  CROSS JOIN UNNEST(json_keys(CAST(g.game.reports AS JSON))) AS k(player_name)
  CROSS JOIN LATERAL (
    SELECT json_extract(CAST(g.game.reports AS JSON), CONCAT('$."', k.player_name, '"')) AS rep_json
  )
  WHERE json_extract_string(CAST(r.participants AS JSON), CONCAT('$."', k.player_name, '"')) IS NOT NULL
) t
WHERE role = 'Seer'
GROUP BY id
ORDER BY "Winrate" DESC, "Games Played" DESC, id

SELECT
  id,
  COUNT(*) AS "Games Played",
  ROUND(AVG(CASE WHEN team_win THEN 1.0 ELSE 0.0 END) * 100.0, 1) AS "Winrate"
FROM (
  SELECT
    json_extract_string(CAST(r.participants AS JSON), CONCAT('$."', k.player_name, '"')) AS id,
    json_extract_string(rep_json, '$.role') AS role,
    CAST(json_extract(rep_json, '$.team_win') AS BOOLEAN) AS team_win
  FROM results r
  CROSS JOIN UNNEST(r.results) AS g(game)
  CROSS JOIN UNNEST(json_keys(CAST(g.game.reports AS JSON))) AS k(player_name)
  CROSS JOIN LATERAL (
    SELECT json_extract(CAST(g.game.reports AS JSON), CONCAT('$."', k.player_name, '"')) AS rep_json
  )
  WHERE json_extract_string(CAST(r.participants AS JSON), CONCAT('$."', k.player_name, '"')) IS NOT NULL
) t
WHERE role = 'Medic'
GROUP BY id
ORDER BY "Winrate" DESC, "Games Played" DESC, id