Window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD: Foundations

Before you can read a single line like ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC), you must know what a table, a row, a column, an ordering, a partition, and a function-that-returns-a-value actually are as pictures. This page builds all of them from zero, in the order they depend on each other.

1. A table = a grid of boxes

The picture: think of a spreadsheet. The row is the whole horizontal band; the column is the whole vertical band; the cell is where they cross.
Why the topic needs it: every window function reads cells (like salary) belonging to rows, and treats columns as the things you ORDER BY or PARTITION BY. If "row" and "column" are fuzzy, nothing later makes sense.

2. `NULL` = the empty box

The picture: a cell drawn as a dashed, empty box (see figure above, the greyed cell).
Why the topic needs it: LAG(revenue, 1, 0) gives 0 for the first row precisely because without that default it would be NULL (there is no previous row → the box is empty). The parent's "sane fallback" only makes sense once you know what it is falling back from.

3. A function that returns a value

The picture: a box with an in-arrow (arguments) and an out-arrow (the returned value).
Empty brackets () means "no arguments needed" — ROW_NUMBER() just counts, it needs nothing handed to it.
LAG(revenue, 1, 0) has three arguments: which column to look at, how many rows back, and what to return if there's nobody there.
Why the topic needs it: all five stars of the show — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD — are function calls. Reading their argument list is step one.

4. Ordering — the `≺` symbol and "strictly ahead"

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

The picture: a queue. Sort salaries descending (ORDER BY salary DESC) and the biggest salary stands at the front. For the row holding 700, the two 900s are strictly ahead of it — they come before it in the line.
Why "strictly"? Strictly ahead means we do not count the row itself or its equals-at-the-same-value. This word is doing all the work in the parent's rank formulas.
Why the topic needs it: the parent defines $RANK (r) = 1 + {x : x ≺ r} .$ You cannot read that line until you know $≺$ means "comes before in the ordering".

5. Set-size bars: ${\dots}$

Reading it slowly: ${x : x ≺ r}$ = "the count of all values $x$ such that $x$ comes strictly before $r$ ".
The picture: point at the queue in Figure 2, cover everyone from r backwards, and count the heads still visible in front. That count is the bars-value.
Why the topic needs it: this is the entire machinery of RANK and DENSE_RANK. Once you can count the things strictly ahead, both formulas are just "1 + that count", differing only in what you count (all rows, vs. distinct values).

6. Distinct values

The picture: rows are individual coins; distinct values are the denominations. Two ₹900 coins are two rows but one denomination.
Why the topic needs it: this single word is the only difference between the two rank formulas:
- RANK counts rows strictly ahead → two 900s each count → the 700 has 2 ahead → rank 3.
- DENSE_RANK counts distinct values strictly ahead → the 900s collapse to one → the 700 has 1 ahead → rank 2.

Recall Check yourself: 800, 800, 800, 600

For the 600 row (descending order): how many rows strictly ahead? ::: 3 (three 800s) So RANK(600) = ? ::: 1 + 3 = 4 How many distinct values strictly ahead? ::: 1 (just the value 800) So DENSE_RANK(600) = ? ::: 1 + 1 = 2

7. Partition = independent sub-queues

The picture: one big line of employees splits into several shorter lines, one per department. Each shorter line gets its own counter starting at 1.
Crucial: partitioning does not delete any rows (unlike WHERE). Everyone still comes out — they are just grouped for the counting.
Why the topic needs it: PARTITION BY dept is what makes rn = 1 mean "top earner in this department" rather than "top earner in the whole company". Without it, the whole result set is one giant partition.

8. Offsets: "n rows before / after" and subscripts $r_{i}$

The picture: the queue again — stand on kid number $i$ ; $r_{i - 1}$ is the kid directly in front, $r_{i + 1}$ is the kid directly behind.
$m$ is just "how many rows this partition has" — the last position.
Why the topic needs it: the parent's LAG/LEAD definition $LAG (co l, n) [r_{i}] = {co l [r_{i - n}] default i - n \geq 1 else$ is only readable once $r_{i}$ , $n$ , and " $i - n \geq 1$ " (does a row that far back even exist?) are pictures. The condition $i - n \geq 1$ just asks: is there still someone standing that far in front? If not → give the default (recall §2, the empty box).

9. Why not just `GROUP BY`? (the shape difference)

Why the topic needs it: this shape difference is the parent's headline claim ("keep every row and get an aggregate"). Everything else — partitions, orderings, ranks — exists only because we refused to collapse the rows.

Prerequisite map

Read it top-down: tables give us rows/columns; ordering gives us "before"; counting-ahead plus distinct give us RANK/DENSE_RANK; partitions and offsets give us per-group and neighbour-peeking. All arrows land on the OVER clause — the parent's central object.

Equipment checklist

Self-test: cover the right side, answer, reveal.

In a table, what is a row vs a column?

A row is one real-world thing across all its properties; a column is one property across all things.

What does NULL mean, and is it the same as 0?

"No value / unknown"; it is NOT zero and NOT empty text — it is the absence of a value.

What do the empty brackets in ROW_NUMBER() mean?

The function needs no arguments — you call it with nothing inside ().

Read $x \prec r$ in plain words.

"x comes strictly before r" in the chosen ordering.

What does "strictly ahead" exclude?

The row itself and any rows tied at its own value.

What do the bars in $\big|\{x : x \prec r\}\big|$ compute?

The count (size) of the set of values strictly before r.

Rows vs distinct values in 900, 900, 700?

Three rows, two distinct values.

Does PARTITION BY delete any rows?

No — it only groups rows so the calculation restarts per group; all rows still come out.

In $r_i$ , what is the subscript i?

The row's position number in the ordered line.

What does the condition $i-n \ge 1$ in LAG check?

Whether a row that many steps back actually exists; if not, return the default.

GROUP BY shape vs window function shape?

GROUP BY = many-in-few-out (collapse); window = many-in-same-many-out (annotate).

When every reveal feels obvious, go read the parent: the topic note.

Connections

GROUP BY and Aggregate Functions — the collapsing "funnel" this page contrasts with the window "stamp".
SQL Logical Query Processing Order — where ordering and partitioning sit in the pipeline.
Common Table Expressions (CTE) — clean containers once you filter on window results.
Self Joins — the old way to fetch a "previous row" before offsets (LAG/LEAD) existed.
Window Frames ROWS vs RANGE — the next layer: controlling how wide the window of neighbours is.
Subqueries and Derived Tables — the wrapper that lets you filter on a computed rank.