Spelling Bee Standings
A school spelling bee wants a standings board. Contestants are ranked by points, highest first. Contestants with equal points share a standing, and the next lower score gets the next consecutive standing: no numbers are skipped after a tie (for example 1, 2, 2, 3).
Return contestant, points and standing for every contestant. Order by points descending, then by contestant name ascending. If there are no contestants, return no rows.
Tables
bee_results — One row per contestant with their final score.
| Column | Type | Description |
|---|---|---|
contestant (key) | TEXT | Contestant name (unique) |
points | INTEGER | Total points earned in the final round, 0 or more |
Example
bee_results
| contestant | points |
|---|---|
| Amara | 42 |
| Bilal | 37 |
| Carmen | 42 |
| Dmitri | 29 |
| Esi | 37 |
| Farid | 45 |
| Greta | 29 |
Expected output
| contestant | points | standing |
|---|---|---|
| Farid | 45 | 1 |
| Amara | 42 | 2 |
| Carmen | 42 | 2 |
| Bilal | 37 | 3 |
| Esi | 37 | 3 |
| Dmitri | 29 | 4 |
| Greta | 29 | 4 |
Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.
SQL Path · Window Functions · problem 38 of 70 · SQLite
The idea
SQL has three ranking functions, and they differ only in how they treat ties. ROW_NUMBER never ties. RANK ties but then skips (1, 2, 2, 4). DENSE_RANK ties and continues with the next integer (1, 2, 2, 3), which is what the board asks for.
DENSE_RANK() OVER (ORDER BY points DESC) computes the standing. The window's ORDER BY only decides how standings are assigned; it does not sort the result, so the query still needs its own ORDER BY points DESC, contestant. The name is the tie-breaker that makes the output order fully defined.
The alternative shows what dense ranking means: a contestant's standing is how many distinct scores are at least as high as theirs. Dropping DISTINCT there would reproduce RANK-style gaps, shifted by the ties.
Solution
SELECT
contestant,
points,
DENSE_RANK() OVER (ORDER BY points DESC) AS standing
FROM bee_results
ORDER BY points DESC, contestant;Another way
SELECT
b.contestant,
b.points,
(SELECT COUNT(DISTINCT o.points)
FROM bee_results AS o
WHERE o.points >= b.points) AS standing
FROM bee_results AS b
ORDER BY b.points DESC, b.contestant;Why common answers fail
RANK leaves gaps after a tie (1, 2, 2, 4), but the board needs consecutive standings.
SELECT contestant, points, RANK() OVER (ORDER BY points DESC) AS standing
FROM bee_results ORDER BY points DESC, contestant;ROW_NUMBER gives tied contestants different standings.
SELECT contestant, points, ROW_NUMBER() OVER (ORDER BY points DESC, contestant) AS standing
FROM bee_results ORDER BY points DESC, contestant;Ranks in ascending order, so the lowest score gets standing 1.
SELECT contestant, points, DENSE_RANK() OVER (ORDER BY points) AS standing
FROM bee_results ORDER BY points DESC, contestant;What the interviewer is checking
A quick check that you know the difference between ROW_NUMBER, RANK and DENSE_RANK, and that window ordering is not result ordering.
Mistakes to avoid
- Using RANK and getting gaps after ties.
- Relying on the window ORDER BY to sort the output.
- Forgetting DESC, so the lowest score ranks first.
Follow-ups to expect
- Show standings per school (PARTITION BY school_id).
- Return only contestants in the top 3 standings (filter the ranked result in an outer query).
- What changes if points could be NULL for absent contestants? (Decide where NULLs sort; SQLite puts them first in ascending order, last in descending.)
In real work
Leaderboards, sales-league tables and "top N tiers" reports all rely on dense ranking so that ties do not create missing positions.