SELECT id, SUM(win) AS Wins, SUM(loss) AS Losses FROM (SELECT t.participants.pro_debater AS id, CASE WHEN r.result.winner='pro_debater' THEN 1 ELSE 0 END AS win, CASE WHEN r.result.winner='con_debater' THEN 1 ELSE 0 END AS loss FROM results t CROSS JOIN UNNEST(t.results) AS r(result) UNION ALL SELECT t.participants.con_debater AS id, CASE WHEN r.result.winner='con_debater' THEN 1 ELSE 0 END AS win, CASE WHEN r.result.winner='pro_debater' THEN 1 ELSE 0 END AS loss FROM results t CROSS JOIN UNNEST(t.results) AS r(result)) GROUP BY id ORDER BY wins DESC, losses ASC, id;