SELECT
  id,
  ROUND(pass_rate, 1) AS "Pass Rate (%)",
  correct_count AS "Correct",
  final_score AS "Final Score",
  max_streak AS "Max Streak",
  ROUND(time_used, 1) AS "Time (s)"
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY pass_rate DESC, final_score DESC, time_used ASC) AS rn
  FROM (
    SELECT
      results.participants.riddle_solver AS id,
      res.pass_rate AS pass_rate,
      res.correct_count AS correct_count,
      res.final_score AS final_score,
      res.max_streak AS max_streak,
      res.time_used AS time_used
    FROM results
    CROSS JOIN UNNEST(results.results) AS r(res)
    WHERE res.pass_rate IS NOT NULL AND res.difficulty = 'medium'
  )
)
WHERE rn = 1
ORDER BY pass_rate DESC, final_score DESC;

SELECT id, ROUND(pass_rate, 1) AS "Pass Rate (%)", 'https://chatgpt.com/' || difficulty_test AS trazability_url, correct_count AS "Correct", final_score AS "Final Score", max_streak AS "Max Streak", ROUND(time_used, 1) AS "Time (s)", difficulty_test AS "Difficulty" FROM (SELECT results.participants.riddle_solver AS id, res.pass_rate AS pass_rate, res.correct_count AS correct_count, res.final_score AS final_score, res.max_streak AS max_streak, res.time_used AS time_used, res.difficulty AS difficulty_test FROM results CROSS JOIN UNNEST(results.results) AS r(res) WHERE res.pass_rate IS NOT NULL) ORDER BY pass_rate DESC, final_score DESC, time_used ASC;

SELECT
  id,
  ROUND(pass_rate, 1) AS "Pass Rate (%)",
  correct_count AS "Correct",
  final_score AS "Final Score",
  max_streak AS "Max Streak",
  ROUND(time_used, 1) AS "Time (s)",
  difficulty AS "Difficulty"
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY pass_rate DESC, final_score DESC, time_used ASC) AS rn
  FROM (
    SELECT
      results.participants.riddle_solver AS id,
      res.pass_rate AS pass_rate,
      res.correct_count AS correct_count,
      res.final_score AS final_score,
      res.max_streak AS max_streak,
      res.time_used AS time_used,
      res.difficulty AS difficulty
    FROM results
    CROSS JOIN UNNEST(results.results) AS r(res)
    WHERE res.pass_rate IS NOT NULL
  )
)
WHERE rn = 1
ORDER BY pass_rate DESC, final_score DESC;