Window Functions — ROW_NUMBER, RANK, LEAD, LAG | Databases Mastery

GROUP BY collapses rows into groups, losing the individual row detail — but a huge class of real questions ("this order's rank within its customer's history," "the running total up to this row") need per-row detail AND an aggregate-style calculation at the same time. Window functions are the only clause in standard SQL built for exactly that.

Before window functions existed in a given SQL dialect, answering "top 3 per group" or "running total" questions required convoluted self-joins or correlated subqueries — technically possible, but slow and hard to read. Window functions express these calculations directly and let the query planner optimize them properly.

ROW_NUMBER() — assigns a unique, sequential number to each row within its partition, with no ties. RANK() — assigns the same rank to tied rows, then skips the next rank number(s). DENSE_RANK() — assigns the same rank to tied rows, without skipping any rank number. LAG()/LEAD() — accesses a prior/following row's value within the same partition, without a self-join.

Window functions perform calculations across rows related to the current row WITHOUT collapsing the result into a single row (unlike GROUP BY aggregates). They are one of the most powerful SQL features for analytics.

Syntax

function_name() OVER (

[PARTITION BY column_list]

[ORDER BY column_list]

[frame_clause]

)

PARTITION BY: divides rows into groups (like GROUP BY but without collapsing).

ORDER BY: defines row order within each partition.

Frame clause: defines the subset of rows to include in the calculation.

Key window functions

ROW_NUMBER() — unique sequential number within partition. No ties.
RANK() — rank with gaps for ties: 1,1,3,4 (skips 2).
DENSE_RANK() — rank without gaps for ties: 1,1,2,3.
NTILE(n) — divides rows into n buckets.
LAG(col, n) — value from n rows BEFORE current row.
LEAD(col, n) — value from n rows AFTER current row.
FIRST_VALUE(col) — first value in the window frame.
LAST_VALUE(col) — last value in the window frame.
NTH_VALUE(col, n) — nth value in the frame.
SUM/AVG/COUNT/MIN/MAX OVER — running totals, moving averages.

Window function execution (after WHERE, GROUP BY, HAVING)

Rows from previous processing stages collected.
PARTITION BY: rows grouped into partitions (no row eliminated).
ORDER BY: rows sorted within each partition.
For each row: window frame defined (default: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for running sum).
Function computed over the frame rows.
Result added to each row as a new column.
Result set returned with all original rows + computed window columns.
Can be filtered in outer query (unlike HAVING which filters aggregates).

Performance note: window functions require sorting (ORDER BY) and potentially multiple passes. For large result sets: ensure ORDER BY columns are indexed.

Step 1: Identify the partitioning column (like GROUP BY but keeps all rows).

Step 2: Identify the ordering within each partition.

Step 3: Choose the function: ranking? running total? previous/next row?

Step 4: Write OVER (PARTITION BY ... ORDER BY ...).

Step 5: Add frame clause if needed (ROWS/RANGE BETWEEN ...).

Step 6: Use the window function result in outer SELECT or WHERE clause.

Step 7: Nest in subquery to filter on window function result.

Window functions vs GROUP BY: window functions keep all rows; GROUP BY collapses. Use window when you need row-level AND group-level data in same result.
Use ROW_NUMBER for deduplication and top-N queries — most flexible because no ties.
Use RANK when tied values should have same rank; DENSE_RANK when you want consecutive ranks.
LAG/LEAD for time-series analysis — much more readable than self-joins.
Frame clause: understand ROWS vs RANGE — ROWS is based on physical row count; RANGE on value equality. Use ROWS for running totals.
Named windows: WINDOW w AS (PARTITION BY dept_id ORDER BY salary) — reuse in multiple functions.
Filter window function results in outer query: SELECT * FROM (...) WHERE rank = 1.
Index ORDER BY columns in window functions for large datasets.
Use FIRST_VALUE / LAST_VALUE with frame: FIRST_VALUE always uses UNBOUNDED PRECEDING; LAST_VALUE needs ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
EXCLUDE clause (PostgreSQL 14+): ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW.

Can't use window function in WHERE — window functions run AFTER WHERE. Put in subquery.
RANK() assumes ORDER BY is provided — without ORDER BY in OVER, result is undefined/meaningless.
LAST_VALUE default frame doesn't cover entire partition — default frame ends at current row. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for last value of entire partition.
Confusing RANK and DENSE_RANK — RANK skips numbers on ties (1,1,3); DENSE_RANK doesn't (1,1,2).
Forgetting PARTITION BY — without it, window covers entire result set (one global partition).
Multiple window functions with same OVER clause — use named WINDOW to avoid repetition.
Performance: window functions cannot use indexes for their frame calculations.

Index ORDER BY columns in OVER clause — avoids explicit sort step.
Materialized CTEs: WITH ... AS MATERIALIZED — evaluate subquery once before window functions.
Filter before window: WHERE clause reduces rows before window function executes.
Multiple window functions with same OVER: named window (WINDOW w AS ...) and reuse — single sort.
Avoid window functions on very large tables without partitioning — consider pre-aggregation.
EXPLAIN shows 'WindowAgg' node — check if sort is needed and if index can help.

No direct security exposure specific to window functions, but a poorly bounded PARTITION BY/ORDER BY on a very large unfiltered dataset can produce an expensive query that a public-facing endpoint could be tricked into triggering repeatedly — apply the same input-validation and rate-limiting discipline you would to any expensive query shape.

Window function queries can silently degrade if the ORDER BY/PARTITION BY columns lose index support after a schema change — periodically re-check EXPLAIN on your most important window-function queries, the same discipline used for any other query shape.

Use window functions for reporting queries — they're more readable and often more efficient than complex self-joins.
For real-time running totals: consider materialized views refreshed periodically.
PostgreSQL: window functions are parallelized in some cases (parallel workers for large datasets).
Monitor sort memory: large window OVER (ORDER BY non-indexed) spills to disk if work_mem insufficient.

Write a query showing each employee's salary, their department's average salary, the difference from the department average, and their rank within the department — all in one query using window functions.
Using the monthly_sales table: calculate month-over-month revenue change (absolute and percentage) using LAG(). Include months where data may be missing with appropriate defaults.
Find the top-2 highest-paid employees per department using ROW_NUMBER(). Then redo with RANK() — observe the difference when there are salary ties.
Calculate a 3-month moving average of revenue and a year-to-date cumulative total using different frame clauses.

Window functions: perform calculations across rows related to the current row WITHOUT collapsing.
ROW_NUMBER(): unique sequential; no gaps. RANK(): ties get same rank, gaps after. DENSE_RANK(): no gaps.
LAG(col, n, default): value from n rows before. LEAD(col, n, default): n rows after.
PARTITION BY: like GROUP BY but keeps all rows. ORDER BY: row order within partition.
Running total: SUM OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
Moving average: AVG OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).
Cannot filter on window function in WHERE — use outer query/subquery.
Named WINDOW clause: avoids repeating identical OVER clauses.

Window Functions — ROW_NUMBER, RANK, LEAD, LAG

Syntax

Key window functions

Window function execution (after WHERE, GROUP BY, HAVING)

Practice quiz

Next Step