SQL Window Functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD
Fast recall
Quick revision
Core answer
Whether you understand the difference between window functions and GROUP BY, can use the OVER clause correctly, and know when to use ROW_NUMBER vs RANK vs DENSE_RANK.
Confusing ROW_NUMBER, RANK, and DENSE_RANK — they behave identically when there are no ties, so developers don't realize the difference until duplicate values cause unexpected numbering.
Show a practical use case like deduplication with ROW_NUMBER (keeping the latest row per group) or running totals with SUM() OVER(ORDER BY), and explain how window functions avoid the self-join anti-pattern.
Interview-ready guide
Interview answer
Window functions solve the problem that GROUP BY can't — you want to compute a rank or running aggregate per group while keeping every individual row visible. GROUP BY collapses rows into one per group; window functions annotate each row with a computed value without removing any of them.
ROW_NUMBER, RANK, DENSE_RANK — the tie-handling difference
- All three assign a sequential number based on
ORDER BYwithin a partition — the difference only appears on ties:ROW_NUMBERassigns unique sequential numbers (no ties possible),RANKskips numbers after a tie (1, 1, 3),DENSE_RANKdoes not skip (1, 1, 2) - The interview question that surfaces this: "find employees sharing the second-highest salary" —
RANKcorrectly labels them rank 2;ROW_NUMBERwould give one rank 2 and the other rank 3 arbitrarily
ROW_NUMBER for deduplication — the most common real-world use
- The pattern:
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)assigns rank 1 to the most recent row per user — thenWHERE rn = 1in an outer query or CTE keeps only the latest record per group - This replaces the correlated subquery or self-join that would otherwise be needed to identify the latest row per group
LAG, LEAD, and running aggregates
LAG(amount, 1) OVER (ORDER BY month)returns the previous row's value — soamount - LAG(amount)computes month-over-month change in one pass without a self-joinSUM(amount) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING)computes a running total in one pass without any self-join or subquery
Window functions execute after WHERE and GROUP BY but before ORDER BY and LIMIT — which means you cannot filter on a window function result directly in WHERE; wrap the query in a CTE or subquery first.
SQL Window Functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD
Direct Answer
Window functions perform calculations across a set of rows related to the current row, without collapsing them into a single output row like GROUP BY does. ROW_NUMBER assigns a sequential number within each partition. RANK and DENSE_RANK handle ties differently — RANK leaves gaps, DENSE_RANK doesn't. LAG and LEAD access values from previous and next rows. All window functions use an OVER clause that defines the partition and ordering.
Common Mistakes & Pitfalls
Confusing ROW_NUMBER, RANK, and DENSE_RANK — they behave identically when there are no ties, so developers don't realize the difference until duplicate values cause unexpected numbering.
Check your understanding
Answer these without looking back at the article.
- How do window functions differ from GROUP BY in execution order?
- What happens if you omit PARTITION BY in the OVER clause?
- How would you use window functions to find gaps in a sequence?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.