Third Best Pinball Score
An arcade logs every game played on its pinball machine. To print a bronze-medal sticker, the owner needs the third highest distinct score ever recorded. If several games share a score, that score counts once: with scores 900, 900, 800 and 700, the third highest distinct score is 700.
Return exactly one row with one column named third_best. If there are fewer than three distinct scores (including when no games have been logged), return NULL in that row.
Tables
pinball_runs — One row per game played.
| Column | Type | Description |
|---|---|---|
run_id (key) | INTEGER | Unique id of the game |
player_tag | TEXT | Three-letter initials the player entered |
points | INTEGER | Final score of the game |
Example
pinball_runs
| run_id | player_tag | points |
|---|---|---|
| 1 | ZAP | 48200 |
| 2 | KIX | 51900 |
| 3 | MOO | 51900 |
| 4 | RAY | 30150 |
| 5 | ZAP | 45000 |
| 6 | JET | 48200 |
Expected output
| third_best |
|---|
| 45000 |
Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.
SQL Path · Subqueries & CTEs · problem 34 of 70 · SQLite
The idea
Sort the distinct scores from highest to lowest, skip two with OFFSET 2 and take one with LIMIT 1. DISTINCT is what makes ties count once. Without it, two games on the same top score would use up two of the three places.
On its own that query returns no row when fewer than three distinct scores exist. Wrapping it as a scalar subquery, SELECT (...) AS third_best, guarantees exactly one row. A scalar subquery that finds nothing becomes NULL.
The alternative peels off the maximum twice: the best score below the best score below the maximum. MAX over an empty set is NULL, and comparing with NULL keeps nothing, so every edge case also ends in a single NULL row. For a general N, DENSE_RANK() is the cleaner tool. RANK() is wrong here because it skips numbers after ties.
Solution
SELECT (
SELECT DISTINCT points
FROM pinball_runs
ORDER BY points DESC
LIMIT 1 OFFSET 2
) AS third_best;Another way
SELECT MAX(points) AS third_best
FROM pinball_runs
WHERE points < (
SELECT MAX(points)
FROM pinball_runs
WHERE points < (SELECT MAX(points) FROM pinball_runs)
);Why common answers fail
Without DISTINCT, repeated scores take up places, so a tie near the top returns the wrong score.
SELECT (
SELECT points FROM pinball_runs ORDER BY points DESC LIMIT 1 OFFSET 2
) AS third_best;Returns no row at all when there is no third score, instead of one row containing NULL.
SELECT DISTINCT points AS third_best
FROM pinball_runs
ORDER BY points DESC
LIMIT 1 OFFSET 2;RANK leaves gaps after ties, so with a tie at the top no score gets rank 3; DENSE_RANK is needed.
SELECT (
SELECT points
FROM (SELECT points, RANK() OVER (ORDER BY points DESC) AS r FROM pinball_runs)
WHERE r = 3
LIMIT 1
) AS third_best;What the interviewer is checking
A variation on the N-th highest value: the interviewer checks your handling of duplicates, of the empty answer, and of the difference between RANK and DENSE_RANK.
Mistakes to avoid
- Forgetting DISTINCT, so ties occupy several places.
- Returning zero rows instead of one NULL row.
- Using RANK instead of DENSE_RANK in a window-function solution.
Follow-ups to expect
- Make N a parameter (LIMIT 1 OFFSET N - 1, or DENSE_RANK() = N).
- Third best score per player tag (DENSE_RANK() OVER (PARTITION BY player_tag ...)).
- Return every game that achieved the third best score (join back on points).
In real work
Podium and leaderboard positions, "third cheapest offer" lookups and percentile-style thresholds all ask for the N-th distinct value.