SQL Window Functions: ROW_NUMBER, RANK, LEAD & LAG

Remember GROUP BY's one iron rule from lesson 3? Rows go into buckets, and the individual rows are gone — you get one summary row per bucket. But what if I ask: "show me every employee, with their salary, and their department's average right next to it"? I want the detail and the summary, together. GROUP BY can't do that. Window functions can — and they're the topic that separates a good SQL round from a great one.

The OVER clause — aggregates that don't collapse

A window function is an ordinary calculation with a magic suffix: OVER (...). The OVER clause says: "for each row, look at a window of related rows, compute, and attach the result to the row — without collapsing anything."

SELECT name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

Alice | 10 | 70000 | 66666.67
Bob   | 10 | 70000 | 66666.67
Carol | 10 | 60000 | 66666.67
David | 20 | 90000 | 85000.00
Eve   | 20 | 80000 | 85000.00

PARTITION BY — GROUP BY's gentler cousin

PARTITION BY dept_id divides rows into per-department windows, exactly like GROUP BY's buckets — with one crucial difference: PARTITION BY groups the calculation, GROUP BY collapses the rows. A 100-row table stays 100 rows with a window function; GROUP BY would shrink it to one row per group. Omit PARTITION BY entirely and the window is the whole table — handy for "each salary vs the company average" in one line.

ROW_NUMBER, RANK, DENSE_RANK — three ways to count to 4

Add ORDER BY inside OVER and rows get positions. Three functions assign them, and the difference only shows up on ties. Salaries 500, 400, 400, 300:

ROW_NUMBER numbers rows blindly (ties broken arbitrarily), RANK gives ties equal rank then skips (like exam merit lists — two people at rank 1 means the next is rank 3), DENSE_RANK gives ties equal rank and never skips. Which to use for "second highest salary, including ties"? DENSE_RANK = 2 — you'll write it in the practice.

LEAD and LAG — looking at your neighbours

"How does this month compare to last month?" — the most-asked question in every business review. LAG(col) fetches the value from the previous row (in the window's order); LEAD(col) from the next:

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_sales;

2026-01 | 50000 | NULL
2026-02 | 55000 |  5000
2026-03 | 52000 | -3000
2026-04 | 60000 |  8000

Running totals — and the frame clause

Now imagine a passbook: each entry shows the balance so far. That's an aggregate over a growing window — from the first row up to the current one. The frame clause says exactly that:

SELECT sale_date, amount,
       SUM(amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales;

Change the frame and you change the question: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a 3-row moving average — the smooth line on every stock chart you've seen. One subtlety for interviews: ROWS counts physical rows, while RANGE groups rows with equal ORDER BY values together as peers — and RANGE is the silent default when you write an ORDER BY with no frame, which occasionally surprises people when ties exist.

Wait — you can't WHERE a window

"Top 3 earners per department" — surely just WHERE rnk <= 3? Try it:

-- WRONG: window results don't exist yet when WHERE runs
SELECT name, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
WHERE rnk <= 3;   -- ERROR

-- RIGHT: compute in a subquery (or CTE), filter outside
SELECT name, dept_id, salary, rnk
FROM (
    SELECT name, dept_id, salary,
           RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk <= 3;

Same execution-order story as lesson 1: window functions are computed with the SELECT list — after WHERE has already filtered. Wrap and filter outside. This wrap-then-filter shape is the skeleton of half of all window-function interview questions.

NTILE — slicing rows into buckets

NTILE(4) deals rows into 4 as-equal-as-possible buckets in order — instant quartiles ("which salary quartile is each employee in?"). When the count doesn't divide evenly, the earlier buckets take the extra rows: 5 rows into NTILE(4) = sizes 2, 1, 1, 1. Fewer rows than buckets? Later bucket numbers simply never get used.

Common mistakes

• Filtering on a window result directly in WHERE — wrap it in a subquery or CTE first.
• Using RANK when the question says "no gaps" — that's DENSE_RANK.
• Using ROW_NUMBER for "including ties" questions — it arbitrarily picks one of the tied rows.
• Forgetting PARTITION BY and ranking the whole company when the question said "per department".
• Assuming LAG on the first row gives 0 — it gives NULL unless you pass a default.

Quick recap

Practice Zone — PYQs from real selection rounds

Ranking, month-over-month deltas, and the dedupe pattern every data engineer uses weekly. Attempt first, reveal second.

Q1Window vs GROUP BYeasy

A 100-row employees table. I run SELECT name, salary, AVG(salary) OVER (PARTITION BY dept_id) FROM employees;. How many rows come back?

Asked in

AOne row per departmentBAll 100 rows — with the department average attached to eachCOne single row with the overall averageDIt's an error — AVG needs GROUP BY

Q2RANK vs DENSE_RANK vs ROW_NUMBERmedium

Salaries in one partition, sorted DESC: 500, 400, 400, 300. What does RANK() assign?

Asked in

A1, 2, 2, 4B1, 2, 2, 3C1, 2, 3, 4D1, 1, 2, 3

Q3LEAD / LAGmedium

LAG(revenue) OVER (ORDER BY month) — what does it return on the very first row?

Asked in

A0BThe first row's own revenueCNULL — unless you pass a default as the third argumentDAn error

Q4Filtering on window resultshard

Why does SELECT name, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees WHERE rnk <= 3; fail?

Asked in

ARANK can't be aliasedBWindow functions are computed during SELECT — after WHERE has already runCRANK needs PARTITION BY to workDIt doesn't fail — this is valid SQL

Q5NTILEhard

NTILE(4) over a partition with 5 ordered rows — what bucket sizes result?

Asked in

A2, 1, 1, 1 — the first bucket takes the extra rowB1, 1, 1, 2 — the last bucket takes the extra rowCIt fails — 5 doesn't divide by 4DAll buckets get 1 row; the 5th row is dropped

FAQ

Next lesson: stacking two result sets on top of each other — Lesson 6: Set Operations →