Talking Through the Ranking Plan
Concepts covered: Row Numbering, Window Partitioning, Common Table Expressions
The SQL for this question is 14 lines, and most candidates can type it. What separates an answer that scores from one that gets a polite nod is the 60 seconds before the typing: a plan said aloud, in the order the engine will run it, with the 4 pieces of the question named. Interviewers on this question are grading whether the pattern was recognized and whether the plan was stated, and the code afterward confirms the plan rather than discovering it.
The plan, in 4 sentences
Those 4 sentences take 30 seconds and carry every decision the query makes. The order matters: group before ranking, ranking before cutoff, because that's the order the engine applies them, and a plan told in that order sounds like someone who has watched the query run. A plan told backward, starting from 'I'll keep rows where rn is at most 3', makes the interviewer wait to find out what rn is, and the wait reads as uncertainty.
A clarifying question or 2 before the plan
The question as asked usually leaves 2 things open, and asking about them scores on its own. First: 'If a department has fewer than 3 people, do you want all of them?' The answer is almost always yes, and asking shows you know the result has a variable row count. Second: 'If 2 people earn the same salary, do you want exactly 3 rows or everyone who ties?' Raising the tie before being asked is the signal, and the tie rule can be settled later. One quicker question is worth adding: 'do you want every column, or only salary with its id and department?' It takes 5 seconds and avoids a rewrite.
- The opening that loses the minute
- Types ORDER BY salary DESC LIMIT 3 and reads back 3 rows from 1 department
- Hears 'where is department 20?' and patches with 1 query per department
- Never says the word partition
- Writes WHERE rn <= 3 under the SELECT that defines rn, and goes quiet when it fails
- Waits to be asked about groups with fewer than 3 rows
- The opening that scores it
- Says 'top N per group' and names the group column before any code
- Says 'ordered by salary descending' as 1 phrase
- Puts ROW_NUMBER inside a CTE and says why the filter sits outside
- Traces 2 departments by hand, 1 with 4 rows and 1 with 2, before saying done
- Asks about the small group and the tie before being asked
Follow-ups and the sentence that answers each
| Follow-up | The answer that scores |
|---|---|
| Why not ORDER BY and LIMIT? | LIMIT counts rows for the whole result; the question needs a count that restarts per department. |
| What does PARTITION BY do here? | It restarts the numbering at 1 every time department_id changes, so each department is ranked on its own. |
| Why is the filter in a separate query? | WHERE runs before the SELECT list, so rn does not exist until the numbering query has finished. |
| What if a department has 2 people? | Both come back; rn <= 3 keeps every row in a group smaller than 3. |
| Could you use a subquery instead of a CTE? | Yes, the same SELECT in the FROM clause; the CTE gives the step a name. |
| What if I want the lowest 3 instead? | Change DESC to ASC in the OVER clause; nothing else moves. |
Narrating the query as it runs
After the code is typed, walk the 6 rows out loud the way the animation did: 'Department 10 sorts to 180, 160, 140, 120, numbered 1 to 4. Department 20 restarts: 90 gets 1, 85 gets 2. The filter keeps 1 through 3, so 104 drops while both of department 20 stay. That's 5 rows.' This trace takes 20 seconds and does 2 things: it catches a missing DESC or a missing PARTITION BY before the interviewer does, and it shows the result was predicted rather than discovered by running it. Interviewers note candidates who say the row count before pressing run.
If a step is forgotten mid-answer, say the plan again from the top rather than searching the screen for the gap. 'Group, then ranking, then cutoff' is short enough to repeat under pressure, and each word points at the clause that carries it: PARTITION BY, ORDER BY inside OVER, WHERE on rn. Guessing at syntax in silence costs more than saying 'the filter has to move to an outer query, give me a second to restructure', because the second sentence shows the plan survived the slip.
Habits worth keeping
- Do
- Write ASC explicitly for cheapest and earliest; highest and latest get DESC, as does any 'most'
- Treat 'latest row per key' deduplication as the same query with rn = 1
- Check the number in the cutoff against the N the question asked for
- Name QUALIFY as a dialect shortcut, and write the CTE that runs on every engine
- Restate group, ranking, cutoff from the top when a step slips
- Avoid
- Type rn < 3 for a top 3, which keeps 2 rows per group
- Drop DESC on a 'highest' question: 104 replaces 101, and department 20 hides the bug
- Read a result back without counting how many distinct group values it holds
- Guess at syntax in silence after a slip; say the plan still holds and restructure
- Use QUALIFY without confirming the engine in the room supports it
About this lesson
This section is part of the Top N Per Group: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.