SQL window functions for top-N and dedupe questions

Why interviewers love this pattern

A window function computes across related rows without collapsing them, so you can rank rows inside a group and still see each row. That one idea answers top 3 products per category, second highest order per customer, and keep the latest record per user.

Say the grain out loud first: one row per what? Then name the partition (the group) and the order (what makes a row first).

Pick the right ranking function

ROW_NUMBER gives every row a unique number, so ties are broken arbitrarily. Use it for dedupe, where you want exactly one row.

RANK leaves gaps after ties (1, 1, 3). DENSE_RANK does not (1, 1, 2). If the prompt says top 3 values and ties should all count, DENSE_RANK is usually what they mean. Ask.

The shape of the answer

Rank in a CTE, then filter in the outer query. You cannot filter on a window function in the same WHERE clause where you compute it.

Add a tiebreaker to ORDER BY (for example the id) so ROW_NUMBER is deterministic, and mention it. Interviewers notice.

Common mistakes

• Filtering on the rank in the same SELECT that defines it.
• Using ROW_NUMBER when the question expects ties to be kept.
• Forgetting a tiebreaker, so results change between runs.
• Joining before ranking and multiplying rows (check join cardinality first).

Further reading

• PostgreSQL docs: Window Functions tutorial
• PostgreSQL docs: Window function reference