Row_number assigns positions; rank leaves gaps after ties; dense_rank does not. Tie-breaking and the meaning of top-N determine the choice.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: Two equal scores share rank one, followed by rank three or dense rank two. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Define top-N meaning
Decide whether ties should increase the returned row count.
Step 2: Choose the ranking function
Row_number assigns positions, rank leaves gaps, dense_rank counts distinct rank groups.
Step 3: Stabilize chosen winners
Use a tie-breaker only when deterministic individual positions are required.
Worked scenario
Two equal scores share rank one, followed by rank three or dense rank two.
Scores 100, 100 and 90 produce ranks 1, 1, 3 and dense ranks 1, 1, 2. Row numbers assign 1, 2, 3 under a defined order. ‘Top two people’ and ‘top two score levels’ therefore ask different questions and can return different row counts.
Common mistake
Row_number over a nonunique order can choose different tied winners.
Verify the behavior
Test all-tied scores and a tie crossing the cutoff; state the expected policy first.
Interview exercise
Return every top-scoring employee.
Answer and reasoning
Use a tie-aware rank or comparison with the group maximum, depending on whether all ties should be retained.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.