beginnerintermediateadvanced

PostgreSQL · Window Functions

Window Functions

OVER()/PARTITION BY without collapsing rows, the ranking and offset function family, running totals vs true moving averages, and when a window function beats a self join

OVER() keeps every rowunlike GROUP BY, nothing is collapsed
RANK leaves gapsDENSE_RANK does not — same ties, different output
Running total: no framemoving average: ROWS BETWEEN N PRECEDING
One pass, no joinPARTITION BY replaces most self joins

1.OVER(), PARTITION BY and ORDER BY in Window Expressions

OVER() turns an aggregate-like function into a window function: it computes across a set of related rows but keeps every row in the output, instead of collapsing them the way GROUP BY does. PARTITION BY defines which rows are "related"; ORDER BY inside OVER() defines the order the window function sees them in.

Key features

OVER() marks a function as a window function — without it, the same function name may mean something else (or not exist) as an aggregate
PARTITION BY splits rows into groups the window function is computed within, without collapsing them
ORDER BY inside OVER() controls the order rows are processed in — required for ranking and offset functions like LAG/LEAD
Omitting PARTITION BY treats the entire result set as one partition
Window functions run after WHERE/GROUP BY/HAVING but before the final ORDER BY and LIMIT

GROUP BY vs a window function, side by side

GROUP BY vs a window function, side by side
Property	GROUP BY	Window function (OVER)
Rows in output	one per group	one per input row
Non-aggregated columns	must appear in GROUP BY	freely available
Typical use	"total per category"	"this row's value, plus its category's total"

GROUP BY collapses rows; a window function keeps them

GROUP BY dept

one output row per department
individual employee rows are gone
SELECT dept, avg(salary) FROM employees GROUP BY dept

avg(salary) OVER (PARTITION BY dept)

one output row per employee, unchanged
each row also shows its department's average
nothing is collapsed

Command

sql

SELECT name, dept, salary,
    avg(salary) OVER (PARTITION BY dept ORDER BY salary) AS running_dept_avg
FROM employees;

OVER() computes across a set of related rows without collapsing them — PARTITION BY groups rows the same way GROUP BY would, but every original row survives in the output.

2.ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and Value Functions

ROW_NUMBER gives every row a unique sequential number. RANK and DENSE_RANK number rows by their ORDER BY value, handling ties differently: RANK leaves gaps after a tie, DENSE_RANK does not. LAG/LEAD read a value from a preceding/following row. FIRST_VALUE/LAST_VALUE/NTH_VALUE pull a value from a specific position in the window.

Key features

ROW_NUMBER never ties — two rows with equal ORDER BY values still get distinct sequential numbers
RANK leaves a gap after ties (1, 2, 2, 4); DENSE_RANK does not (1, 2, 2, 3)
LAG(col, n) and LEAD(col, n) read a value n rows before/after the current row within its partition, defaulting to n=1
LAG/LEAD return NULL when there is no row at that offset (e.g. LAG on the first row of a partition)
All of these require ORDER BY inside OVER() — without it, "position" and "preceding row" are undefined

How each function handles the same tied values

How each function handles the same tied values
Rank by	Sequence for values 90, 90, 80
ROW_NUMBER()	1, 2, 3 — ties broken arbitrarily
RANK()	1, 1, 3 — gap after the tie
DENSE_RANK()	1, 1, 2 — no gap

Four families of window function, one question each

ROW_NUMBER

position, no ties

RANK / DENSE_RANK

position, ties respected

LAG / LEAD

a neighboring row's value

FIRST/LAST/NTH_VALUE

a fixed position's value

Command

sql

SELECT name, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
    RANK() OVER (ORDER BY score DESC) AS rnk,
    LAG(score) OVER (ORDER BY score DESC) AS prev_score
FROM results;

ROW_NUMBER never ties; RANK leaves gaps after ties; DENSE_RANK does not — LAG/LEAD read a neighboring row's value and return NULL when no such row exists in the partition.

3.Running Totals, Moving Averages and Top-N-Per-Group

A running total is SUM() OVER (ORDER BY ...) — each row adds itself to everything before it. A top-N-per-group pattern uses ROW_NUMBER() OVER (PARTITION BY group ORDER BY rank_col) inside a subquery or CTE, then filters WHERE row_number <= N in the outer query.

Key features

SUM(col) OVER (ORDER BY date) with no PARTITION BY produces a running total across the whole result set
Adding PARTITION BY to a running total resets it per group, e.g. a running total per customer
Top-N-per-group requires a subquery or CTE: window functions cannot be referenced in the same SELECT's WHERE clause
PERCENTILE_CONT / PERCENTILE_DISC are ordered-set aggregates, not window functions, though they answer a related question
A moving average over a fixed window of rows needs an explicit frame, e.g. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

Pattern → the window function shape that produces it

Pattern → the window function shape that produces it
Goal	Shape
Running total	SUM(col) OVER (ORDER BY date)
Running total per group	SUM(col) OVER (PARTITION BY grp ORDER BY date)
Top 2 per group	ROW_NUMBER() OVER (PARTITION BY grp ORDER BY val DESC) in a CTE, then WHERE rn <= 2

Command

sql

-- running total
SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total
FROM payments;

-- top 2 earners per department
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, dept, salary FROM ranked WHERE rn <= 2;

A running total is just SUM() with ORDER BY instead of no ORDER BY; top-N-per-group is ROW_NUMBER() partitioned by group, filtered in an outer query — window functions cannot be filtered in their own SELECT's WHERE clause.

4.Window Frames: ROWS and RANGE Conceptually

A frame is the subset of rows within a partition that a window function actually looks at for the current row — by default, everything from the partition's start up to the current row (when ORDER BY is present). ROWS BETWEEN N PRECEDING AND CURRENT ROW narrows that to a fixed-size window, which is how a true moving average is built.

Key features

The default frame, when ORDER BY is present, is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS counts physical rows; RANGE groups peer rows with equal ORDER BY values into the same frame boundary
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a fixed 3-row moving window, unlike RANGE's value-based grouping
With no ORDER BY at all, the default frame is the entire partition — every row sees the same full-partition value
RANGE with duplicate ORDER BY values can include more rows than expected, since ties share the same frame boundary

ROWS vs RANGE — how they treat tied ORDER BY values

ROWS vs RANGE — how they treat tied ORDER BY values
Frame mode	Boundary counted by	Behavior on ties
ROWS	physical row position	each row is its own boundary, regardless of ties
RANGE	ORDER BY value	tied rows share the same frame boundary

Command

sql

SELECT date, amount,
    AVG(amount) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3day
FROM payments;

The default frame (with ORDER BY) is "start of partition through current row" — that is why plain SUM() OVER (ORDER BY ...) is a running total. Use an explicit ROWS BETWEEN N PRECEDING AND CURRENT ROW frame for a true fixed-size moving window.

5.When Window Functions Beat Self Joins or Nested Queries

Anything answerable with a window function can usually also be written as a self join or a correlated subquery — but the window function version is typically both clearer to read and a single pass over the data, while the self join/subquery version often reprocesses the same rows once per outer row.

Key features

A self join computing "each employee vs their department's max salary" needs a GROUP BY subquery joined back — a window function needs one PARTITION BY
A correlated subquery inside SELECT runs once per outer row conceptually; a window function is computed in a single pass over its partition
Window functions can express things self joins cannot directly, like ROW_NUMBER for arbitrary row ordering within a group
Self joins remain the right tool when the relationship is not a simple "compare to my own group," e.g. genuinely comparing two different rows' full column sets side by side
Prefer a window function first for any "this row vs its group" question; reach for a self join only when the comparison needs two full, independently-filterable row shapes side by side

Same question, two approaches

Same question, two approaches
Approach	Shape
Self join	join employees to a GROUP BY subquery of itself, matching on dept
Window function	MAX(salary) OVER (PARTITION BY dept) — one pass, no join

Command

sql

-- window function version of the same question
SELECT name, salary, MAX(salary) OVER (PARTITION BY dept) AS dept_max
FROM employees;

A window function expresses "this row vs its own group" in one pass, no join required — reach for a self join only when the comparison genuinely needs two independently-filterable row shapes side by side.

Spotted a mistake on this page? Tell us.

Read Sheet