SELECT id,
       COUNT(*) AS "Role appearances",
       SUM(won) AS "Wins",
       ROUND(100.0 * AVG(won), 1) AS "Win rate (%)",
       ROUND(AVG(total_score), 3) AS "Mean judge total"
FROM (
  SELECT CAST(participants.pro_debater AS VARCHAR) AS id, 'Pro' AS debate_role,
         res.pro_debater.total_score AS total_score,
         CASE WHEN res.winner = 'pro_debater' THEN 1 ELSE 0 END AS won
  FROM results
  CROSS JOIN UNNEST(results.results) AS t(res)
  WHERE results.status = 'completed'
  UNION ALL
  SELECT CAST(participants.con_debater AS VARCHAR) AS id, 'Con' AS debate_role,
         res.con_debater.total_score AS total_score,
         CASE WHEN res.winner = 'con_debater' THEN 1 ELSE 0 END AS won
  FROM results
  CROSS JOIN UNNEST(results.results) AS t(res)
  WHERE results.status = 'completed'
) AS role_results
WHERE id IS NOT NULL AND id <> ''
GROUP BY id
ORDER BY "Win rate (%)" DESC, "Mean judge total" DESC, id