Window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD: Question bank

Before we start, one word we will lean on: a partition is the slice of rows one call of the function is allowed to "see" — set by PARTITION BY, and it is the whole result set if you omit that clause.

True or false — justify

A window function returns fewer rows than it received when many rows tie.

False — a window function is N-rows-in, N-rows-out no matter what; ties only affect the number it prints, never the row count. Only GROUP BY collapses rows.

RANK() and DENSE_RANK() always agree when there are no ties.

True — with all-distinct values every row has a unique set of rows ahead, so "rows strictly ahead" equals "distinct values strictly ahead"; the gap-skipping only shows up once two rows share a value.

ROW_NUMBER() without ORDER BY still gives a well-defined, repeatable numbering.

False — with no order the engine is free to number rows any way it likes, so two runs of the same query can differ. Ranking/offset functions need an ORDER BY to be deterministic.

PARTITION BY dept filters the output down to one department at a time.

False — it only resets the calculation boundary per department; every row from every department still comes out. It is a scope, not a WHERE.

Putting WHERE row_num = 1 in the same SELECT that defines row_num is valid SQL.

False — WHERE runs before window functions in the logical order, so the alias does not exist yet; you must wrap it in a subquery, CTE, or use QUALIFY.

LAG(x) with no ORDER BY inside OVER returns a meaningful "previous" value.

False — "previous" is defined by the window's order; with no ORDER BY there is no notion of before/after, so the result is arbitrary or an error depending on the engine.

An aggregate like SUM(x) OVER (...) collapses rows just like SUM(x) with GROUP BY.

False — the OVER (...) turns the aggregate into a window aggregate: it computes the sum but writes it beside every original row, keeping them all.

If two salaries tie for 2nd place, RANK() will still eventually produce the number 3 somewhere.

False — those two both get rank 2 and the next distinct value jumps to rank 4; the value 3 is skipped entirely. Use DENSE_RANK() if you need no gaps.

LEAD(x, 1) on the last row of a partition returns the first row of the next partition.

False — offset functions never cross a partition boundary; on the last row LEAD returns the default (or NULL if none is given).

Spot the error

SELECT name, ROW_NUMBER() FROM employees; — what breaks?

ROW_NUMBER() legally requires an OVER (...) clause; without it the function is not a window function and the query is rejected. Even an empty OVER () would parse.

... WHERE RANK() OVER (ORDER BY salary DESC) <= 3 — why won't this run?

Window functions are evaluated after WHERE, so you cannot reference one inside WHERE; move it into a derived table or CTE and filter on its alias outside.

Someone wants "top 3 distinct salary levels" and writes WHERE rn <= 3 where rn = ROW_NUMBER(). Bug?

ROW_NUMBER gives 3 people, not 3 salary levels — if the top salary is shared it returns duplicate values and misses lower distinct levels. Use DENSE_RANK() <= 3.

LAG(revenue) OVER (ORDER BY day) then revenue - prev — why is the first delta NULL?

The first row has no predecessor, so LAG returns NULL, and any arithmetic with NULL is NULL. Supply a default like LAG(revenue, 1, 0) to get a real number.

AVG(salary) OVER (PARTITION BY dept ORDER BY salary) unexpectedly gives a running average, not the department average. Why?

Adding ORDER BY to a window aggregate silently installs a default frame (up to the current row), turning it into a running calc; drop the ORDER BY (or set an explicit full frame) for the whole-partition average — see Window Frames ROWS vs RANGE.

ROW_NUMBER() OVER (PARTITION BY dept) used to pick "the top earner per dept" returns a random employee each run. Why?

With no ORDER BY salary DESC, rn = 1 lands on an arbitrary row, not the highest salary; add the ordering so position 1 is the top earner.

Why questions

Why does RANK() skip numbers after a tie but DENSE_RANK() does not?

RANK = 1 + (rows strictly ahead), so two tied rows both count toward "rows ahead" of the next one; DENSE_RANK = 1 + (distinct values ahead), which counts each value only once, leaving no gap.

Why prefer a window function over a self join for "previous row's value"?

LAG/LEAD read the neighbour directly along the window order in one pass, avoiding the extra join condition, potential duplicate matches, and the cost of a self join.

Why can PARTITION BY and ORDER BY appear together in one OVER clause?

They answer different questions — PARTITION BY says which group resets the calc, ORDER BY says in what sequence to walk within that group — so ranking restarts per partition and follows the order inside it.

Why do window functions run after SELECT's filtering but you still write them in the SELECT list?

Logically they are computed on the rows that survived WHERE/GROUP BY, which is exactly the stage the SELECT projection describes; writing them there matches when they actually execute.

Why is choosing between ROW_NUMBER, RANK, and DENSE_RANK a semantic decision, not a style one?

They answer literally different questions — "position in line" vs "how many are ahead" vs "how many distinct levels are ahead" — so the wrong one silently returns the wrong rows for ties.

Edge cases

What does a ranking function return for an empty partition?

Nothing — if a partition has zero rows there is no row to attach a number to, so no output is produced for it; numbers are per existing row only.

What is LAG(x, 0) — the offset of zero?

An offset of 0 returns the current row's own value of x, since it looks 0 rows back; rarely useful but not an error.

For a partition with a single row, what do RANK, DENSE_RANK, and ROW_NUMBER all return?

All return 1 — one row is trivially first, has nothing ahead of it, and is the only distinct value, so the three functions coincide.

If every value in the ordering column is identical, what does RANK() give every row?

Every row is tied for first, so all get rank 1; DENSE_RANK also gives 1, but ROW_NUMBER still counts 1, 2, 3, … because it never ties.

Does LAG(col, n, default) return the default only on the very first row?

No — it returns the default on any row where looking n positions back would fall outside the partition, so the first n rows all get the default.

What happens to NULL values in the ORDER BY of a ranking window?

They are ordered together according to the engine's NULLS FIRST/NULLS LAST rule and ranked like any other tied group; they do not vanish, so control their placement explicitly if it matters.