When would you reach for a SQL window function instead of GROUP BY, and how do ROW_NUMBER, RANK, and DENSE_RANK differ?

technical-conceptual · Mid level · data-ml

What the interviewer is really asking

Assesses whether the candidate understands that window functions keep every row while GROUP BY collapses them, and can pick the right ranking function based on how ties should be handled.

What to say

• Draw the core distinction: GROUP BY collapses rows into one summary row per group, so you lose row-level detail; a window function computes across a partition but keeps every row and adds a column — so use it when you need both the aggregate (or rank) and the original rows.
• Separate the three ranking functions by tie behaviour: ROW_NUMBER gives every row a unique sequential number and breaks ties arbitrarily; RANK gives tied rows the same rank but then skips numbers (1,1,3); DENSE_RANK gives ties the same rank with no gaps (1,1,2).
• Tie the choice to the question: ROW_NUMBER for deduplication or 'pick exactly one per group', DENSE_RANK when 'top N distinct values' must stay contiguous through ties, RANK when you specifically want gaps to reflect how many rows tied above.

What to avoid

• Claiming GROUP BY and window functions are interchangeable — they return different row counts and serve different needs.
• Saying ROW_NUMBER, RANK, and DENSE_RANK are the same thing with different names, ignoring the tie-handling that is the entire point of the distinction.
• Using ROW_NUMBER to pick the 'top earner' when there could be ties, silently dropping a co-leader the business expected to see.

Example answers

Strong: To get the latest order row per customer I used ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) and filtered to rn = 1 — that keeps the full row, which a GROUP BY MAX(order_ts) would have collapsed and forced me to join back to recover.

Weak: I'd just GROUP BY and use MAX — window functions are basically the same and I find them confusing.