Window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD: Worked examples

This page is the "no surprises" drill for the parent topic. We enumerate every kind of case a window-function question can throw at you, then work each one until nothing is left unseen. If you meet a case in an exam that isn't below, we failed — but we won't.

Before we start, two pieces of notation we lean on constantly:

The scenario matrix

Every window-function problem is one (or a blend) of these case classes. The right column names the example that nails it.

#	Case class	What makes it tricky	Covered by
C1	No ties, plain counter	baseline — all three rankers agree	Ex 1
C2	Ties present	RANK skips, DENSE_RANK packs, ROW_NUMBER is arbitrary	Ex 2
C3	PARTITION resets	counter restarts per group	Ex 3
C4	Filtering on a window value	window runs after `WHERE` → must wrap	Ex 4
C5	LAG/LEAD boundary (first & last row)	no predecessor / successor → default vs NULL	Ex 5
C6	Offset $n > 1$ and out-of-range	jumping 2+ rows past the edge	Ex 6
C7	Degenerate: single row / empty partition	limiting input — does it break?	Ex 7
C8	Real-world word problem	translate English → window	Ex 8 (running total)
C9	Exam twist: "top-3" ambiguity	distinct levels vs people — wrong ranker fails	Ex 9
C10	NULLs in the ORDER BY column	where do NULLs sort?	Ex 10

We use one tiny table throughout so you can trace every number by eye.

The figure below draws exactly this table as two salary "skylines", one per department. Look at it now: the burnt-orange bars are dept A (four people), the teal bars are dept B (two people), and the two shaded lanes are the two partitions we will keep splitting on. Every later example refers back to these six bars.

Figure — Window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD

Ex 1 — C1: no ties, plain counter

Forecast: with no ties, do the three functions differ at all? Guess before reading.

List positions. Salaries descending → line them up: 900 is $r_{1}$ , 800 is $r_{2}$ , ... 500 is $r_{5}$ . Why this step? Ranking is meaningless without an order; the position is the ROW_NUMBER.
Apply ROW_NUMBER. Just count heads: $1, 2, 3, 4, 5$ . Why? ROW_NUMBER = "your place in the queue", nothing more.
Apply RANK using the parent's formula $RANK (r) = 1 + ∣ {x : x ≺ r} ∣$ (1 plus the number of rows strictly ahead, where $≺$ is our "comes before" symbol from the definition above). The 700 has exactly 2 rows ahead (900, 800) → rank 3. Why? No two salaries are equal, so "rows ahead" and "position minus one" are the same number.
Apply DENSE_RANK $= 1 + ∣ {distinct v ≺ r} ∣$ . For 700, distinct values ahead are ${900, 800}$ → 2 → rank 3. Why? Every value is distinct, so "distinct values ahead" = "rows ahead" again.

Verify: all three give $1, 2, 3, 4, 5$ . When there are no ties, ROW_NUMBER = RANK = DENSE_RANK. That's the baseline every other case bends away from.

Ex 2 — C2: ties present

Forecast: Ana and Ben both earn 900. Where does Cy (700) land under each ranker — 2 or 3?

Order & spot the tie. $r_{1} = 900, r_{2} = 900, r_{3} = 700, r_{4} = 500$ . The tie is at positions 1 and 2. Why? The rankers only diverge because of ties — find them first.
ROW_NUMBER: $1, 2, 3, 4$ . The two 900s get 1 and 2 arbitrarily. Why? It refuses ties by design; the DB breaks the tie however it likes (non-deterministic — see mistake below).
RANK for Cy (700): rows strictly ahead (values $≺ 700$ ) = both 900s = 2, so $1 + 2 = 3$ . Why? Two people occupy "1st place", so the next distinct value starts at 3 — the gap is the skipped number 2. The two 900s are tied, so they are "beside" each other, not "before" — hence they share rank 1.
DENSE_RANK for Cy: distinct values ahead = ${900}$ = 1, so $1 + 1 = 2$ . Why? Only one level (900) is ahead, so Cy is at level 2 — no gap.

Verify:

salary	ROW_NUMBER	RANK	DENSE_RANK
900	1	1	1
900	2	1	1
700	3	3	2
500	4	4	3

RANK's last value is 4, DENSE_RANK's is 3 — exactly matching the parent table.

Ex 3 — C3: PARTITION resets

Worked example Statement

Rank everyone within their own department by salary descending, using ROW_NUMBER.

SELECT dept, name, salary,
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM staff;

Forecast: dept A has 4 people, dept B has 2. What is the largest rn you'll see anywhere?

Split into partitions. A = {Ana 900, Ben 900, Cy 700, Dee 500}; B = {Eve 800, Fin 600}. Why? PARTITION BY dept says: compute the counter independently per department — a per-group reset, not a filter (all 6 rows still come out).
Order inside each partition by salary DESC. Why? rn=1 should be the top earner of that dept.
Count within A: Ana 1, Ben 2, Cy 3, Dee 4.
Count within B (restart at 1): Eve 1, Fin 2. Why? The counter resets at each new partition — that's the entire point of PARTITION BY.

The next figure makes that reset visible. Look at the plum arrow: after the last row of the orange lane (dept A, rn=4) it loops back down to the first row of the teal lane (dept B), where the counter drops back to rn=1. The two lanes are the two partitions; notice that no bar is deleted — every one of the six people still gets a row, they just get their own per-lane count.

Verify: rows returned = 6 (none removed → PARTITION ≠ WHERE). Max rn = 4 (from the larger partition A). Two rows have rn=1 (one per dept), which is what "top per dept" needs.

Ex 4 — C4: filtering on a window value

Forecast: Will SELECT ... WHERE ROW_NUMBER() OVER(...) = 1 even run?

Recall the logical order from SQL Logical Query Processing Order: FROM → WHERE → GROUP BY → HAVING → window functions → SELECT (projection) → DISTINCT → ORDER BY. Why? Window functions are their own evaluation phase that runs after FROM/WHERE/GROUP BY/HAVING have finished (they need the fully filtered, grouped row set to look at neighbours), but before the final SELECT list is projected and before ORDER BY. The key fact for us: this phase is strictly later than WHERE, so while WHERE is running, the alias rn has not been produced yet.
Wrap it. Compute rn in an inner query (or CTE), filter outside:
```
WITH ranked AS (
  SELECT dept, name, salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM staff)
SELECT dept, name, salary FROM ranked WHERE rn = 1;
```
Why the CTE? In the outer query, rn is already a finished plain column produced by the inner query — so the outer WHERE can touch it. See Common Table Expressions (CTE) and Subqueries and Derived Tables.

Verify: dept A top is Ana (or Ben — tie!) at 900; dept B top is Eve at 800. Result = 1 row per dept. If the Ana/Ben tie must be deterministic, add , id to the ORDER BY so Ana (id 1) wins.

Ex 5 — C5: LAG/LEAD at the first and last row

Forecast: day 1 has nobody in front; day 3 has nobody behind. What appears in those two blank cells?

Order by day $r_{1} (100), r_{2} (150), r_{3} (120)$ , so $m = 3$ . Why? "Previous"/"next" only mean something along an order — LAG/LEAD read the window's ORDER BY.
Apply the general LAG rule from the top of the page with $n = 1, d = 0$ : $LAG [r_{i}] = co l [r_{i - 1}]$ if $i - 1 \geq 1$ , else $0$ .
- $r_{1}$ : $i - n = 0 < 1$ → fell off the front → use default 0.
- $r_{2}$ : revenue of $r_{1}$ = 100. $r_{3}$ : revenue of $r_{2}$ = 150. Why the 0? Without a default, the missing predecessor is NULL, which would poison delta.
delta = revenue − prev: $100 - 0 = 100$ , $150 - 100 = 50$ , $120 - 150 = - 30$ . Why keep the sign? A negative delta ( $- 30$ ) legitimately means revenue fell — don't clamp it.
Apply the general LEAD rule with $n = 1$ and no default: $LEAD [r_{i}] = co l [r_{i + 1}]$ if $i + 1 \leq m$ , else NULL.
- $r_{1} \to 150$ , $r_{2} \to 120$ , $r_{3}$ : $i + n = 4 > 3 = m$ → fell off the back → NULL. Why NULL here? We supplied no 3rd argument, so the out-of-range case falls back to NULL.

Verify:

day	rev	prev	delta	next
1	100	0	100	150
2	150	100	50	120
3	120	150	−30	NULL

This is exactly the Self Joins "previous row" pattern — but with no join.

Ex 6 — C6: offset n > 1 and out-of-range

Forecast: with a jump of 2, how many rows at the start fall off the edge?

Positions: $r_{1} (100), r_{2} (150), r_{3} (120), r_{4} (200)$ , so $m = 4$ .
Apply the general LAG rule with $n = 2, d = - 1$ : $LAG [r_{i}] = co l [r_{i - 2}]$ if $i - 2 \geq 1$ else $- 1$ :
- $r_{1}$ : $1 - 2 = - 1 < 1$ → default $- 1$ .
- $r_{2}$ : $2 - 2 = 0 < 1$ → default $- 1$ .
- $r_{3}$ : $3 - 2 = 1$ → revenue of $r_{1}$ = 100.
- $r_{4}$ : $4 - 2 = 2$ → revenue of $r_{2}$ = 150. Why do TWO rows get the default? A jump of $n$ leaves the first $n$ rows without a valid source — here $n = 2$ so rows 1 and 2 both fall off.

Verify: LAG-2 column = $[- 1, - 1, 100, 150]$ . Count of default cells = $n = 2$ . ✓

Ex 7 — C7: degenerate single-row / empty partition

Forecast: With only Zoe present, is her rank 0 or 1? Does LAG error out?

Single row, all rankers: rows ahead ( $≺$ Zoe) = 0, distinct values ahead = 0 → all give $1 + 0 = 1$ . Why? The formulas count others ahead of you; with nobody ahead the count is 0, so rank = 1 (never 0).
LAG(salary,1,0) for Zoe via the general rule with $m = 1, i = 1, n = 1$ : $i - n = 0 < 1$ → out of range → default 0. Why not an error? Boundary misses are defined behaviour: return the default (or NULL). A lone row is just "all boundary".
Empty partition: if no rows match, the function produces zero output rows — there is no row to attach a value to. Why? Window functions annotate existing rows; no rows, nothing to annotate. It does not error and does not invent a row.

Verify: Zoe → ROW_NUMBER=1, RANK=1, DENSE_RANK=1, LAG=0. Empty partition → 0 rows. ✓

Ex 8 — C8: real-world word problem (running total)

Worked example Statement

"For each day, show revenue and the cumulative revenue so far." Revenues by day: 100, 150, 120.

SELECT day, revenue,
  SUM(revenue) OVER (ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM sales;

Forecast: on day 2, is running = 150 (just today) or 250 (day1+day2)?

Read "so far" as a frame. "Cumulative up to me" = the window frame from the first row through the current row — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. See Window Frames ROWS vs RANGE. Why a frame, not GROUP BY? GROUP BY would collapse days into one number; we need every day kept with its own running sum — a window (contrast GROUP BY and Aggregate Functions).
Accumulate along the order:
- day1: sum of {100} = 100.
- day2: sum of {100,150} = 250.
- day3: sum of {100,150,120} = 370. Why does the sum grow? Each row's frame includes all earlier rows plus itself.

Verify: running column = $[100, 250, 370]$ ; the final value 370 equals the plain total $100 + 150 + 120$ . Units: currency in = currency out. ✓

Ex 9 — C9: exam twist — "top 3" ambiguity

Forecast: With two pairs tied, will WHERE r <= 3 return 3 rows, 4 rows, or fewer levels than you expect?

Compute all three columns. Ordered DESC:

salary ROW_NUMBER RANK DENSE_RANK

900 1 1 1

900 2 1 1

800 3 3 2

800 4 3 2

700 5 5 3

600 6 6 4
Filter ROW_NUMBER <= 3 → rows 1,2,3 = salaries $900, 900, 800$ . Meaning: top 3 people/rows (arbitrary among ties).
Filter RANK <= 3 → ranks $1, 1, 3, 3$ ≤ 3 → salaries $900, 900, 800, 800$ = 4 rows. The "3rd distinct level" (700) is skipped because RANK jumped from 3 to 5. Why does 700 vanish? RANK skipped rank 4, so <=3 grabbed both 800s but never reaches 700's rank of 5.
Filter DENSE_RANK <= 3 → dense-ranks $1, 1, 2, 2, 3$ ≤ 3 → salaries $900, 900, 800, 800, 700$ = 5 rows. Meaning: top 3 distinct salary levels (900, 800, 700), all people at those levels.

Verify: row counts = ROW_NUMBER→3, RANK→4, DENSE_RANK→5. Distinct salary levels under DENSE_RANK ≤ 3 = ${900, 800, 700}$ , exactly 3 levels. "Top 3 distinct levels" ⇒ DENSE_RANK; "top 3 people" ⇒ ROW_NUMBER. ✓

Ex 10 — C10: NULLs in the ORDER BY column

Forecast: A NULL means "we don't know the value". Does it sort as the biggest (front of the DESC queue), the smallest (back), or throw an error? Guess before reading.

Understand what NULL is. NULL is not 0 and not "empty text" — it is the marker for "unknown". SQL never silently treats it as a number, so it cannot obey the normal "bigger comes first" rule of DESC. It has to be placed by a separate policy. Why this step? If you assume NULL = 0, you'd predict Ben sorts last (0 is the smallest salary) — and you'd be wrong on half the engines. The value's position is undefined by the number line, so a policy decides it.
Name the policy: NULLS FIRST vs NULLS LAST. Every engine has a default, and the defaults disagree (e.g. PostgreSQL treats DESC as NULLS FIRST; some others differ). Because it is engine-dependent, the safe habit is to write it explicitly: ORDER BY salary DESC NULLS LAST. Why be explicit? Portability. The identical query would otherwise rank Ben differently across databases — a silent correctness bug, not an error message.
Case A — NULLS LAST. Non-NULL salaries sort DESC first: 900 (Ana), 700 (Cy); then the NULL (Ben) is parked at the back. ROW_NUMBER walks the line 1,2,3. What it looks like: Ana → 1, Cy → 2, Ben → 3. Why? "Last" literally means "put the unknowns at the tail of the queue", so Ben gets the largest position.
Case B — NULLS FIRST. The NULL (Ben) is parked at the front, then the non-NULLs DESC: 900 (Ana), 700 (Cy). ROW_NUMBER walks 1,2,3. What it looks like: Ben → 1, Ana → 2, Cy → 3. Why? "First" puts unknowns at the head, so Ben grabs position 1 even though we know nothing about his salary.
What about the rankers on the NULL itself? RANK/DENSE_RANK count values $≺$ current. Under NULLS LAST, Ben has both real salaries ahead of him → RANK = $1 + 2 = 3$ ; under NULLS FIRST, nobody is ahead of Ben → RANK = 1. Why? The $≺$ relation follows whatever ordering (including the NULL policy) you declared.

Verify:

policy	Ana	Cy	Ben
`NULLS LAST`	1	2	3
`NULLS FIRST`	2	3	1

Ben's ROW_NUMBER is 3 under NULLS LAST and 1 under NULLS FIRST — two different correct answers, proving you must state the NULL policy. No crash occurs either way; NULL ordering is defined behaviour, just engine-defaulted.

Recall Self-test: name the case class

Which ranker for "top 3 distinct price levels"? ::: DENSE_RANK (filter <= 3). LAG(x,2,-1) on the 2nd row of a table — value? ::: The default -1 (2-2=0 < 1, out of range). Running total keeps N rows or collapses them? ::: Keeps all N — it's a windowed SUM, not GROUP BY. A single-row partition: RANK = ? ::: 1 (zero rows ahead → 1+0). Why wrap a window function to filter on it? ::: The window phase runs after WHERE, so the alias doesn't exist in WHERE — use a CTE/subquery. NULL in ORDER BY salary DESC — where does it go? ::: Engine-dependent; state NULLS FIRST/LAST explicitly. What does the symbol $≺$ mean here? ::: "comes strictly before in the ORDER BY sequence" — ties do not count.

Connections

4.4.09 Window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD (Hinglish) — the parent topic in Hinglish.
SQL Logical Query Processing Order — why Ex 4's WHERE fails.
Common Table Expressions (CTE) / Subqueries and Derived Tables — the wrapping trick.
Window Frames ROWS vs RANGE — the frame used in Ex 8.
Self Joins — the old way to do Ex 5's LAG.
GROUP BY and Aggregate Functions — the collapsing contrast.