SELECT CAST(t.participants.participant AS VARCHAR) AS id, MAX(TRY_CAST(r.result.participants.participant.summary.average_score AS DOUBLE)) AS score, MAX(TRY_CAST(r.result.participants.participant.summary.passed AS BIGINT)) AS passed, MAX(TRY_CAST(r.result.participants.participant.summary.total AS BIGINT)) AS total, MAX(TRY_CAST(r.result.participants.participant.summary.errors AS BIGINT)) AS errors FROM results t CROSS JOIN UNNEST(t.results) AS r(result) WHERE CAST(t.participants.participant AS VARCHAR) <> '00000000-0000-0000-0000-000000000000' GROUP BY id ORDER BY score DESC, id

SELECT CAST(t.participants.participant AS VARCHAR) AS id, MAX(TRY_CAST(r.result.participants.participant.summary.passed AS DOUBLE)) / NULLIF(MAX(TRY_CAST(r.result.participants.participant.summary.total AS DOUBLE)), 0) AS score, MAX(TRY_CAST(r.result.participants.participant.summary.passed AS BIGINT)) AS passed, MAX(TRY_CAST(r.result.participants.participant.summary.total AS BIGINT)) AS total FROM results t CROSS JOIN UNNEST(t.results) AS r(result) WHERE CAST(t.participants.participant AS VARCHAR) <> '00000000-0000-0000-0000-000000000000' GROUP BY id ORDER BY score DESC, id

SELECT CAST(t.participants.participant AS VARCHAR) AS id, 1.0 AS score FROM results t WHERE CAST(t.participants.participant AS VARCHAR) <> '00000000-0000-0000-0000-000000000000' GROUP BY id ORDER BY id