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 (%)",
  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
    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
)
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;