All three number rows within a window. ROW_NUMBER gives every row a unique sequential number, so ties are broken arbitrarily. RANK gives tied rows the same number then skips ahead, producing 1, 2, 2, 4. DENSE_RANK gives tied rows the same number without skipping, producing 1, 2, 2, 3. Use ROW_NUMBER for deduplication and RANK or DENSE_RANK for genuine leaderboards.
Why interviewers ask this
It is a quick, unambiguous SQL fluency check with no room to waffle, and the practical follow through matters just as much: picking one row per group is an everyday task and ROW_NUMBER is the right tool for it. Interviewers also want you to notice that ROW_NUMBER is nondeterministic on ties unless your ORDER BY includes an explicit tiebreaker.
How to structure your answer
- State the numbering behavior of each with the 1, 2, 2, 4 example.
- Map each function to the task it is right for.
- Warn that ROW_NUMBER on ties needs a deterministic tiebreaker.
- Show the standard pattern for picking the latest row per group.
Example answer
ROW_NUMBER always gives distinct integers, so two tied rows still get 1 and 2 in whatever order the engine feels like. RANK gives them both 1 and then jumps to 3. DENSE_RANK gives them both 1 and continues at 2. In practice I use ROW_NUMBER far more than the other two, because the most common job is deduplication: partition by customer_id, order by updated_at descending, take where row_number equals 1 to get the latest record per customer. If I am building a top ten list where ties should share a position, that is DENSE_RANK. The gotcha with ROW_NUMBER is determinism. If updated_at is identical for two rows, which one you keep can change between runs, and I have seen a dashboard flicker between two values for exactly that reason. So I add a second sort key, usually the primary key, to make it stable and reproducible.
Walking into this interview soon? GhostPilot listens to your live call, spots the question the moment it is asked, and puts a structured answer on your screen in real time. Try it on your next mock, or grab a $29 Session Pass, no subscription, for the real thing.
See how it worksFollow-up questions to expect
- How would you pick the top three products per category?
- What happens with ROW_NUMBER if two rows have identical sort values?
- How does NTILE differ from these three?
Related data analyst questions
Your interviewer will ask their own version of this. Paste your actual job description into the free Question Predictor and get the 20 questions that role is most likely to ask, with what each one is really probing.
Predict my questions