SELECT id, Avg_Score AS Score, Avg_Composition AS Composition, Avg_Probability AS Probability, Avg_Completeness AS Completeness, Avg_Aesthetics AS Aesthetics, Avg_Originality AS Originality, Avg_Fairness AS Fairness, Avg_Fun AS Fun, Avg_Difficulty AS Difficulty FROM (SELECT id, AVG(score) AS Avg_Score, AVG(composition) AS Avg_Composition, AVG(probability) AS Avg_Probability, AVG(completeness) AS Avg_Completeness, AVG(aesthetics) AS Avg_Aesthetics, AVG(originality) AS Avg_Originality, AVG(fairness) AS Avg_Fairness, AVG(fun) AS Avg_Fun, AVG(difficulty) AS Avg_Difficulty, ROW_NUMBER() OVER (PARTITION BY id ORDER BY AVG(score) DESC) as id_rank FROM (SELECT id, created_at, score, composition, probability, completeness, aesthetics, originality, fairness, fun, difficulty, ROW_NUMBER() OVER (PARTITION BY id, created_at ORDER BY score DESC) as item_rank FROM (SELECT results.participants.agent AS id, res.created_at, map_res.score, map_res.task_rewards.composition, map_res.task_rewards.probability, map_res.task_rewards.completeness, map_res.task_rewards.aesthetics, map_res.task_rewards.originality, map_res.task_rewards.fairness, map_res.task_rewards.fun, map_res.task_rewards.difficulty FROM results CROSS JOIN UNNEST(results.results) AS t(res) CROSS JOIN UNNEST(res.history) AS h(map_res))) WHERE item_rank <= 25 GROUP BY id, created_at) WHERE id_rank = 1 ORDER BY Score DESC

SELECT id, Run_Date, AVG(score) AS Score, AVG(composition) AS Composition, AVG(probability) AS Probability, AVG(completeness) AS Completeness, AVG(aesthetics) AS Aesthetics, AVG(originality) AS Originality, AVG(fairness) AS Fairness, AVG(fun) AS Fun, AVG(difficulty) AS Difficulty FROM (SELECT id, created_at AS Run_Date, score, composition, probability, completeness, aesthetics, originality, fairness, fun, difficulty, ROW_NUMBER() OVER (PARTITION BY id, created_at ORDER BY score DESC) as rn FROM (SELECT results.participants.agent AS id, res.created_at, map_res.score, map_res.task_rewards.composition, map_res.task_rewards.probability, map_res.task_rewards.completeness, map_res.task_rewards.aesthetics, map_res.task_rewards.originality, map_res.task_rewards.fairness, map_res.task_rewards.fun, map_res.task_rewards.difficulty FROM results CROSS JOIN UNNEST(results.results) AS t(res) CROSS JOIN UNNEST(res.history) AS h(map_res))) WHERE rn <= 25 GROUP BY id, Run_Date ORDER BY Run_Date DESC