SELECT id, CONCAT(CAST(ROW_NUMBER() OVER (ORDER BY score DESC) AS VARCHAR), CASE WHEN ROW_NUMBER() OVER (ORDER BY score DESC) % 100 IN (11, 12, 13) THEN 'th' WHEN ROW_NUMBER() OVER (ORDER BY score DESC) % 10 = 1 THEN 'st' WHEN ROW_NUMBER() OVER (ORDER BY score DESC) % 10 = 2 THEN 'nd' WHEN ROW_NUMBER() OVER (ORDER BY score DESC) % 10 = 3 THEN 'rd' ELSE 'th' END) AS 'Rank', competition_id AS 'Competition', PRINTF('%.5f', score) AS 'Score', CASE WHEN gold_medal THEN 'Gold 🥇' WHEN silver_medal THEN 'Silver 🥈' WHEN bronze_medal THEN 'Bronze 🥉' ELSE '-' END AS 'Medal', CASE WHEN above_median THEN 'Yes' ELSE 'No' END AS 'Above Median', PRINTF('%.3f', gold_threshold) AS 'Gold Req.', SUBSTR(created_at, 1, 19) AS 'Submitted At' FROM ( SELECT CAST(results.participants.agent AS VARCHAR) AS id, res.competition_id, res.score, res.gold_medal, res.silver_medal, res.bronze_medal, res.above_median, res.gold_threshold, res.created_at FROM results CROSS JOIN UNNEST(results.results) AS r(res) WHERE results.participants.agent IS NOT NULL ) AS agent_metrics ORDER BY score DESC;