Window functions & ranking
SQL · 6 interview questions
A window function computes across a set of related rows while keeping every row in the output. That's the entire distinction from GROUP BY: grouping collapses ten rows into one, a window function annotates all ten with something computed from all ten.
OVER (PARTITION BY x ORDER BY y) is the whole syntax. PARTITION BY splits the rows into independent windows — it's the GROUP BY of the window world — and ORDER BY defines the sequence within each one, which ranking and offset functions need.
The three ranking functions differ only in how they treat ties, and interviewers ask about it constantly. ROW_NUMBER never ties: it assigns 1, 2, 3, 4 arbitrarily among equal values. RANK ties and then skips: 1, 1, 3. DENSE_RANK ties and doesn't skip: 1, 1, 2. "Top three salaries" almost always means DENSE_RANK, because three distinct salary levels is the intent, not three people.
LAG and LEAD reach into the previous or next row of the window, which turns "compare each day to the day before" from a self-join into one line. Once you've seen it, a whole class of self-join problems stops needing a self-join.
Window functions & ranking interview questions
- How is a window function different from GROUP BY?
- GROUP BY collapses each group into a single row. A window function computes over the same set of related rows but returns a value on every row, leaving the row count unchanged. That's why you can show a row and its group's average side by side.
- Why they ask: The one-sentence answer that proves the concept landed.
- What's the difference between ROW_NUMBER, RANK and DENSE_RANK?
- On values 10, 10, 20: ROW_NUMBER gives 1, 2, 3 (ties broken arbitrarily); RANK gives 1, 1, 3 (ties share, then skip); DENSE_RANK gives 1, 1, 2 (ties share, no gap). 'Top N distinct values' means DENSE_RANK.
- Why they ask: Asked verbatim in a large share of SQL interviews. Answer with the example, not the prose.
- What does PARTITION BY do?
- It splits rows into independent windows, so the function restarts for each one — ranking per department rather than across the whole company. It's the window equivalent of GROUP BY, but it doesn't reduce the row count.
- Why they ask: 'Highest paid per department' is unanswerable without it.
- Why can't you filter on a window function in the WHERE clause?
- Window functions are evaluated after WHERE, at roughly the same stage as SELECT, so the value doesn't exist yet. Compute it in a subquery or CTE and filter on it in the outer query —
WHERE rnk <= 3. - Why they ask: Everyone hits this the first time they write a top-N-per-group query.
- What problem do LAG and LEAD solve?
- They read a value from a row offset from the current one within the window — the previous day's temperature, the next login. They replace a self-join on
id = id - 1, which is both slower and wrong whenever the ids aren't perfectly consecutive. - Why they ask: The 'wrong when ids have gaps' point is the one worth making.
- Are window functions available in MySQL?
- From MySQL 8.0 onward, yes. Before that they don't exist, which is why so much older MySQL uses correlated subqueries or session variables to fake ROW_NUMBER. LeetCode runs 8.0, so window functions are fair game there.
- Why they ask: Knowing the version boundary explains a lot of strange legacy SQL.
Practise it
Free, database-tagged LeetCode problems that drill this specific skill. In the app these tick themselves off from your solve history.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start your streakFull refund within 7 days. No questions.