Four students take an exam: Alice scores 95, Bob scores 92, Carol scores 92, Dave scores 88. Using DENSE_RANK() ORDER BY score DESC, what ranks are assigned?
AAlice=1, Bob=2, Carol=3, Dave=4
BAlice=1, Bob=2, Carol=2, Dave=4
CAlice=1, Bob=2, Carol=2, Dave=3
DAlice=1, Bob=3, Carol=3, Dave=4
DENSE_RANK assigns equal ranks to tied values and does NOT skip subsequent ranks. Bob and Carol both score 92, so they both receive rank 2. Dave receives rank 3 — the next consecutive rank — not rank 4. Option B (answer index 1) describes RANK behavior, which skips rank 3 because two people share rank 2. Option A describes ROW_NUMBER, which assigns unique integers ignoring ties. DENSE_RANK's defining feature is consecutive rank values regardless of ties.
Question 2 Multiple Choice
You want to deduplicate a customer table by keeping exactly one row per customer — the most recently updated record. Which ranking function is most appropriate and why?
ARANK(), because it handles ties by assigning the same rank to duplicates
BDENSE_RANK(), because it produces consecutive integers that make filtering easy
CROW_NUMBER(), because it guarantees exactly one row receives rank 1 per partition
DAny of the three work equally well for this use case
ROW_NUMBER is the right choice here because deduplication requires exactly one row to receive rank 1 per customer — even if two rows have identical updated_at timestamps. ROW_NUMBER always assigns unique sequential integers, so exactly one row per PARTITION BY group gets row number 1. RANK and DENSE_RANK would assign the same rank to tied updated_at values, meaning filtering WHERE rank = 1 could return multiple rows per customer. When you need 'pick exactly one,' ROW_NUMBER is the only safe choice.
Question 3 True / False
When two rows have identical values in the ORDER BY clause, both RANK and DENSE_RANK will assign them the same rank number.
TTrue
FFalse
Answer: True
Both RANK and DENSE_RANK respect ties by assigning equal rank values to rows that are equal in the ORDER BY expression. This is what distinguishes them from ROW_NUMBER, which always assigns unique integers even to tied rows. The difference between RANK and DENSE_RANK is not in how they handle the tied rows themselves, but in what rank number they assign to the row immediately after the tie: RANK skips ranks, DENSE_RANK does not.
Question 4 True / False
Adding a PARTITION BY clause to a ranking window function causes most rows in the entire query result to share a single global rank sequence.
TTrue
FFalse
Answer: False
PARTITION BY causes the ranking function to restart independently for each partition — the opposite of sharing a global sequence. For example, RANK() OVER (PARTITION BY department ORDER BY salary DESC) restarts at rank 1 for each department. Without PARTITION BY, the ranking operates across all rows as a single group. PARTITION BY is precisely the mechanism for computing rankings within groups rather than globally.
Question 5 Short Answer
What is the practical difference between RANK and DENSE_RANK, and in what scenario would each be the more appropriate choice?
Think about your answer, then reveal below.
Model answer: RANK skips rank numbers after ties (two people tie for 2nd, next is 4th); DENSE_RANK does not skip (two people tie for 2nd, next is 3rd). Use RANK when you want to match real-world ranking conventions where tied positions consume slots (sports standings, competition results). Use DENSE_RANK when you want to count distinct performance tiers — the DENSE_RANK value tells you how many distinct rank levels exist above the current row.
The key is what the rank number communicates. RANK's gaps convey 'n people ranked above you' — it answers 'what position are you in?' DENSE_RANK's consecutive values convey 'n distinct levels above you' — it answers 'how many tiers are you from the top?' Neither is universally better; the choice depends on the semantic meaning of 'rank' required by your use case.