SQL Roadmap/Window Functions

Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK

PARTITION BY resets a calculation per group the way GROUP BY does, but without collapsing rows — and the three ranking functions differ in exactly one thing: how they handle ties.

!!!3/5Theory: 25m3 problems

The shape every window function shares: OVER()

A window function is any function followed by an OVER(...) clause. That clause is what makes it a window function instead of a regular one — it defines the set of rows ("the window") the function considers for each individual output row, without collapsing them the way GROUP BY does:

SELECT employee_id, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank FROM employees;

Every row of the original table survives in the output — RANK() just adds one more computed column to each. PARTITION BY is the direct window-function analogue of GROUP BY: it resets the ranking to start over for each department, exactly the way GROUP BY department_id would start a fresh aggregate per department, except no rows are collapsed. Omit PARTITION BY entirely and the window is the whole result set — one single ranking across every row.

The three ranking functions differ only in tie-handling

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, RANK() OVER (ORDER BY score DESC) AS rank, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank FROM scores;

For scores [100, 90, 90, 80]:

scoreROW_NUMBERRANKDENSE_RANK
100111
90222
90322
80443
  • ROW_NUMBER() never ties — every row gets a unique, strictly sequential number, even when the underlying values are identical. Which of two tied rows gets 2 vs. 3 is arbitrary unless the ORDER BY includes a fully unique tiebreaker column.
  • RANK() gives tied rows the same rank, then skips the next rank(s) by the number of ties (two rows tied at rank 2 means the next distinct value gets rank 4, not 3) — this matches how humans usually describe competition rankings ("joint second, so the next one is fourth").
  • DENSE_RANK() also gives tied rows the same rank, but does not skip afterward — the next distinct value gets the very next integer.

Picking the wrong one for a problem that cares about ties is the single most common mistake with this function family — "second highest salary" and "second highest distinct salary" are different questions, and RANK() vs DENSE_RANK() is exactly the lever that answers each correctly.

Ranking functions as the modern greatest-N-per-group tool

The correlated-subquery "highest salary per department" pattern from the previous topic has a direct, often cleaner window-function equivalent:

SELECT * FROM ( SELECT employee_id, department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk FROM employees ) ranked WHERE rnk <= 3; -- top 3 salaries per department, ties included

Note the window function has to be computed in a subquery (or CTE) before you can filter on it — you cannot reference a window function's result directly in the same SELECT's WHERE clause, because WHERE runs before window functions are evaluated in SQL's logical execution order (window functions are conceptually computed right before the final SELECT, after WHERE/GROUP BY/HAVING). This is exactly why "Department Top Three Salaries" and the earlier correlated-subquery "Department Highest Salary" are worth solving both ways — same underlying question, and the contrast makes both techniques, and their respective tradeoffs, concrete.

Why interviewers care

This is the topic most likely to be the actual differentiator in a senior SQL interview, precisely because most candidates are comfortable with joins and GROUP BY but meaningfully fewer default to a window function when one is the cleaner tool. An interviewer who asks "find the top 3 earners per department, with ties" is specifically checking whether you reach for DENSE_RANK() OVER (PARTITION BY ...) — the fluent, single-pass answer — or fall back to a much clunkier correlated-subquery-with-COUNT construction.

Pitfalls and interview gotchas

  • Using ROW_NUMBER() when the problem implies ties matter ("all employees tied for 2nd place") — ROW_NUMBER() arbitrarily breaks every tie, silently dropping legitimate tied rows if you then filter WHERE row_num = 2.
  • Confusing RANK()'s gap-after-ties behavior with DENSE_RANK()'s no-gap behavior, and picking the wrong one for a "Nth highest" question — "Nth highest salary" (allowing duplicates to not count as a step) is a DENSE_RANK() question, not a RANK() one.
  • Trying to filter on a window function directly in WHERE in the same SELECT rather than wrapping it in a subquery/CTE first.
  • Forgetting PARTITION BY and getting a global ranking when a per-group ranking was intended (or vice versa).

Where this goes next

Running Totals, Moving Averages & Frame Clauses (next) covers the other major window-function family: functions whose result depends on ordered position relative to the current row rather than tie-handling — cumulative sums, moving averages, and comparing a row directly to the row before/after it.

Further Resources (Optional)

Practice Problems

Interview relevancy:!!!Critical — must-have!!Important — highly recommended!Good to have~Niche — rarely asked

Optional Practice (Extra Reps)

For once you've cleared the main set above and want more reps on this pattern. These don't count toward the roadmap's progress stats — solve them purely for your own benefit.