Marathon Podium Times per Age Group

A city marathon awards medals per age group to the three fastest distinct finish times. Everyone who ran one of those times gets a medal, so a group can have more than three medallists when runners finish in identical times. A lower finish_seconds is faster.

Runners with NULL in finish_seconds did not finish and never get a medal. A group with fewer than three distinct times gives medals to all of its finishers.

Return label, runner_name and finish_seconds for every medallist, in any order. Age groups with no finishers do not appear.

Tables

age_groups — One row per age group category.

finishers — One row per registered runner.

Example

age_groups

finishers

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Window Functions · problem 42 of 70 · SQLite

The idea

"Top three distinct values per group, with everyone who has them" is exactly what DENSE_RANK computes: tied times share a place and the next distinct time gets the next place, so places 1 to 3 are the three fastest distinct times.

Partition by group_id and order by finish_seconds ascending, because smaller is faster. Before ranking, drop runners with no time. This matters in SQLite: NULL sorts before every number in ascending order, so a non-finisher would otherwise take place 1 and push a real finisher off the podium.

Filter place <= 3 in an outer query and join to age_groups for the label.

The alternative states the rule directly: a finisher medals when fewer than three distinct faster times exist in their group. Comparisons with NULL are never true, so non-finishers never count as faster, and the outer IS NOT NULL keeps them out of the result.

Solution

WITH ranked AS (
  SELECT
    group_id,
    runner_name,
    finish_seconds,
    DENSE_RANK() OVER (PARTITION BY group_id ORDER BY finish_seconds) AS place
  FROM finishers
  WHERE finish_seconds IS NOT NULL
)
SELECT g.label, r.runner_name, r.finish_seconds
FROM ranked AS r
JOIN age_groups AS g ON g.group_id = r.group_id
WHERE r.place <= 3;

Another way

SELECT g.label, f.runner_name, f.finish_seconds
FROM finishers AS f
JOIN age_groups AS g ON g.group_id = f.group_id
WHERE f.finish_seconds IS NOT NULL
  AND (
    SELECT COUNT(DISTINCT f2.finish_seconds)
    FROM finishers AS f2
    WHERE f2.group_id = f.group_id
      AND f2.finish_seconds < f.finish_seconds
  ) < 3;

Why common answers fail

RANK skips places after a tie, so after two runners share 1st the third distinct time is ranked 4th and misses out.

WITH ranked AS (
  SELECT group_id, runner_name, finish_seconds,
         RANK() OVER (PARTITION BY group_id ORDER BY finish_seconds) AS place
  FROM finishers WHERE finish_seconds IS NOT NULL
)
SELECT g.label, r.runner_name, r.finish_seconds
FROM ranked AS r JOIN age_groups AS g ON g.group_id = r.group_id
WHERE r.place <= 3;

Keeps non-finishers: SQLite sorts NULL first in ascending order, so a runner who did not finish takes 1st place.

WITH ranked AS (
  SELECT group_id, runner_name, finish_seconds,
         DENSE_RANK() OVER (PARTITION BY group_id ORDER BY finish_seconds) AS place
  FROM finishers
)
SELECT g.label, r.runner_name, r.finish_seconds
FROM ranked AS r JOIN age_groups AS g ON g.group_id = r.group_id
WHERE r.place <= 3;

ROW_NUMBER stops at exactly three runners per group, so tied runners lose their medals.

WITH ranked AS (
  SELECT group_id, runner_name, finish_seconds,
         ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY finish_seconds) AS place
  FROM finishers WHERE finish_seconds IS NOT NULL
)
SELECT g.label, r.runner_name, r.finish_seconds
FROM ranked AS r JOIN age_groups AS g ON g.group_id = r.group_id
WHERE r.place <= 3;

What the interviewer is checking

The interviewer is checking that you pick DENSE_RANK over RANK or ROW_NUMBER for "top N distinct values", and that you think about NULL ordering.

Mistakes to avoid

• Using RANK or ROW_NUMBER instead of DENSE_RANK.
• Ranking in descending order when lower times are better.
• Letting NULL finish times into the ranking.

Follow-ups to expect

• Show the place (1, 2 or 3) next to each medallist (select the dense rank itself).
• Return each group's medal times as one comma-separated string (group_concat over the distinct medal times).
• Make NULLs sort last without filtering them (ORDER BY finish_seconds IS NULL, finish_seconds, or NULLS LAST).

In real work

Podium results, top-3 products per category and tiered bonus bands per team all need the top N distinct values per group with ties kept.

Practice next