SELECT id, CONCAT(CAST(ROW_NUMBER() OVER (ORDER BY pass_power_3 DESC) AS VARCHAR), CASE WHEN ROW_NUMBER() OVER (ORDER BY pass_power_3 DESC) % 100 IN (11, 12, 13) THEN 'th' WHEN ROW_NUMBER() OVER (ORDER BY pass_power_3 DESC) % 10 = 1 THEN 'st' WHEN ROW_NUMBER() OVER (ORDER BY pass_power_3 DESC) % 10 = 2 THEN 'nd' WHEN ROW_NUMBER() OVER (ORDER BY pass_power_3 DESC) % 10 = 3 THEN 'rd' ELSE 'th' END) AS "Rank", CONCAT('(#', CAST(submission_num AS VARCHAR), ')') AS "Run", COALESCE(LTRIM(PRINTF('%.2f', pass_power_3), '0'), '-') AS "Overall Pass^3", LTRIM(PRINTF('%.2f', base_pass_power_1), '0') AS "Base Pass^1", COALESCE(LTRIM(PRINTF('%.2f', base_pass_power_3), '0'), '-') AS "Base Pass^3", COALESCE(LTRIM(PRINTF('%.2f', base_pass_at_3), '0'), '-') AS "Base Pass@3", LTRIM(PRINTF('%.2f', hall_pass_power_1), '0') AS "Hallucination Pass^1", COALESCE(LTRIM(PRINTF('%.2f', hall_pass_power_3), '0'), '-') AS "Hallucination Pass^3", COALESCE(LTRIM(PRINTF('%.2f', hall_pass_at_3), '0'), '-') AS "Hallucination Pass@3", LTRIM(PRINTF('%.2f', dis_pass_power_1), '0') AS "Disambiguation Pass^1", COALESCE(LTRIM(PRINTF('%.2f', dis_pass_power_3), '0'), '-') AS "Disambiguation Pass^3", COALESCE(LTRIM(PRINTF('%.2f', dis_pass_at_3), '0'), '-') AS "Disambiguation Pass@3", CAST(ROUND(time_used, 1) AS VARCHAR) AS "Time (s)" FROM ( SELECT CAST(results.participants.agent AS VARCHAR) AS id, ROW_NUMBER() OVER (PARTITION BY results.participants.agent ORDER BY res.pass_power_k_scores."Pass^3" DESC) AS submission_num, res.pass_power_k_scores."Pass^3" AS pass_power_3, res.time_used AS time_used, res.pass_power_k_scores_by_split.base."Pass^1" AS base_pass_power_1, res.pass_power_k_scores_by_split.base."Pass^3" AS base_pass_power_3, res.pass_at_k_scores_by_split.base."Pass@3" AS base_pass_at_3, res.pass_power_k_scores_by_split.hallucination."Pass^1" AS hall_pass_power_1, res.pass_power_k_scores_by_split.hallucination."Pass^3" AS hall_pass_power_3, res.pass_at_k_scores_by_split.hallucination."Pass@3" AS hall_pass_at_3, res.pass_power_k_scores_by_split.disambiguation."Pass^1" AS dis_pass_power_1, res.pass_power_k_scores_by_split.disambiguation."Pass^3" AS dis_pass_power_3, res.pass_at_k_scores_by_split.disambiguation."Pass@3" AS dis_pass_at_3 FROM results CROSS JOIN UNNEST(results.results) AS r(res) WHERE results.participants.agent IS NOT NULL ) AS agent_metrics ORDER BY pass_power_3 DESC;