SELECT id, MAX(avg_score) AS score, MAX(passed) AS passed, MAX(total) AS total FROM (SELECT t.participants.participant AS id, CAST(r.result.participants.participant.summary.average_score AS DOUBLE) AS avg_score, CAST(r.result.participants.participant.summary.passed AS BIGINT) AS passed, CAST(r.result.participants.participant.summary.total AS BIGINT) AS total FROM results t CROSS JOIN UNNEST(t.results) AS r(result)) GROUP BY id ORDER BY score DESC, id

SELECT id, MAX(passed) * 1.0 / NULLIF(MAX(total), 0) AS score, MAX(passed) AS passed, MAX(total) AS total FROM (SELECT t.participants.participant AS id, CAST(r.result.participants.participant.summary.passed AS BIGINT) AS passed, CAST(r.result.participants.participant.summary.total AS BIGINT) AS total FROM results t CROSS JOIN UNNEST(t.results) AS r(result)) GROUP BY id ORDER BY score DESC, id