hard School Top Achievers by Subject

DENSE_RANK() hands every row a rank by walking the ordered scores within each partition and incrementing the rank only when the score value changes — so equal scores collapse onto the same rank and no rank numbers are skipped. That single property is the whole problem: "top three unique scores" is the definition of "DENSE_RANK <= 3", and getting it right depends entirely on which of the three ranking functions you reach for.

Problem

Two tables. Student(id, name, score, subjectId) records one student's score in one subject; Subject(id, name) names each subject. Return, per subject, every student whose score is among the top three distinct scores for that subject. Ties on a qualifying score all stay in — so a subject can return more than three rows.

The query

Rank inside each subject, then keep ranks 1–3. The ranking has to happen in a subquery (or CTE) because a window function cannot appear in a WHERE clause — WHERE is evaluated before the window functions are computed.

SELECT su.name  AS Subject,
       st.name  AS Student,
       st.score AS Score
FROM (
    SELECT subjectId,
           name,
           score,
           DENSE_RANK() OVER (
               PARTITION BY subjectId
               ORDER BY score DESC
           ) AS s_rank
    FROM Student
) st
JOIN Subject su ON st.subjectId = su.id
WHERE st.s_rank <= 3
ORDER BY su.name, st.score DESC;

PARTITION BY subjectId restarts the ranking for each subject; ORDER BY score DESC makes rank 1 the highest score; the outer WHERE s_rank <= 3 keeps the top three distinct score tiers.

Worked trace on the real data

Math (subjectId 1) holds Alice 92, Derek 95, Elisa 88, Fiona 92, George 90. Walk them in score DESC order and assign DENSE_RANK, bumping the rank only when the score drops to a new value:

The 92 tie shares rank 2 and does not consume rank 3, so George at 90 still qualifies and Elisa at 88 lands at rank 4 and is cut. Science (subjectId 2) has only Carol 87 (rank 1) and Bob 85 (rank 2); both fit inside three. Final result: Math → Derek 95, Alice 92, Fiona 92, George 90; Science → Carol 87, Bob 85.

Why the obvious alternatives are wrong

The diagram shows the divergence on the 92/92/90 stretch. Each function counts differently:

• ROW_NUMBER() gives every row a distinct number even on ties (1,2,3,4,5). Alice and Fiona both scored 92 but get ranks 2 and 3 in an arbitrary, non-deterministic order. <= 3 then keeps one of the two 92s and silently drops the other — the result depends on tie-break luck and breaks the "top three unique scores" contract.
• RANK() shares the rank on ties but then skips: 1,2,2,4,5. The two 92s eat rank 3, so George at 90 becomes rank 4 and is cut by <= 3 — you'd return only the top two distinct scores' worth of students, not three.
• DENSE_RANK() shares on ties and does not skip: 1,2,2,3,4. The 92 tier is rank 2, the 90 tier is rank 3, the 88 tier is rank 4. <= 3 lands exactly on the top three distinct scores. This is the only function that matches the spec.

Pitfalls

• Window function in WHERE. WHERE DENSE_RANK() OVER (...) <= 3 is a syntax error in every standard SQL engine — window functions are computed after WHERE/GROUP BY, in the SELECT/ORDER BY phase. You must compute the rank in a subquery or CTE and filter in the outer query. (QUALIFY exists as sugar for this in Snowflake/BigQuery/DuckDB, but not in MySQL or Postgres.)
• Reaching for RANK by reflex. RANK is the famous "Olympic" ranking and feels like the default, but its gap behavior quietly returns fewer score tiers than asked. Whenever the requirement says "top N distinct values," it is DENSE_RANK, not RANK.
• Forgetting the partition. Drop PARTITION BY subjectId and you rank across the whole school — Science's 87 and 85 would lose to Math's high scores and vanish entirely. The partition is what makes it per-group.
• NULL scores. With ORDER BY score DESC, Postgres sorts NULLs first (rank 1) unless you add NULLS LAST; MySQL sorts them last. A missing score can silently steal a top-three slot. Add NULLS LAST or a WHERE score IS NOT NULL filter when scores can be absent.
• Calling this "hard." It is the canonical top-N-per-group pattern (LeetCode 185 family). The only judgment call is RANK vs DENSE_RANK; once you internalize the gap rule, every "top N per category" report is the same query.

Takeaways

• "Top N distinct values per group" ⇒ DENSE_RANK() OVER (PARTITION BY group ORDER BY metric DESC) filtered <= N in an outer query.
• Ties decide the function: ROW_NUMBER breaks ties arbitrarily, RANK keeps gaps, DENSE_RANK keeps ties without gaps. Pick by whether you want unique rows, Olympic ranking, or distinct value tiers.
• Window functions can't live in WHERE — they run after it; wrap in a subquery/CTE (or use QUALIFY where available).
• The same skeleton solves "top 3 salaries per department," "latest N orders per customer," and "highest-priced item per category."

Re-authored and deepened for this guide. Based on LeetCode's "Department Top Three Salaries" / "Nth Highest Salary" problem family and the SQL window-function specification (ISO/IEC 9075, OVER / PARTITION BY / ORDER BY). Ranking-function semantics cross-checked against the PostgreSQL documentation (Window Functions) and MySQL 8.0 reference (Window Function Descriptions). The grader-noted trace — Math ties at 92 sharing rank 2, George at 90 surviving as rank 3, Elisa at 88 dropped at rank 4 — was verified by hand against the supplied data, and the RANK / ROW_NUMBER / DENSE_RANK contrast that the original page asserted in one line is now demonstrated explicitly.