Every common SQL window function and the clauses that control them, grouped by job: rank rows, peek at neighbours, run totals across a frame, and pull first or last values. Each row pairs the function with a one-line example.
A window function computes a result across rows related to the current row, without collapsing them the way GROUP BY does. Every window function ends in an OVER clause that defines the window: PARTITION BY groups rows, ORDER BY sequences them, and the optional frame bounds the sliding set. Master OVER and the rest is vocabulary.
Reference table · 27 entries
SQL Window FunctionsExplained
27 of 27 rows
Ranking
A unique sequential integer per row within the window.
ROW_NUMBER() OVER (ORDER BY total DESC)
Rank with gaps; ties share a rank and skip the next numbers.
RANK() OVER (ORDER BY total DESC)
Rank with no gaps; ties share a rank and nothing is skipped.
DENSE_RANK() OVER (ORDER BY total DESC)
Splits the ordered window into n roughly equal buckets.
NTILE(4) OVER (ORDER BY total DESC)
Peek at neighbours
A value from n rows back; the default replaces it when no such row exists.
LAG(total, 1, 0) OVER (ORDER BY created_at)
A value from n rows ahead; the default replaces it when no such row exists.
LEAD(total, 1, 0) OVER (ORDER BY created_at)
Aggregate over a window
A running or partitioned total without collapsing rows.
SUM(total) OVER (ORDER BY created_at)
A running or partitioned average.
AVG(total) OVER (PARTITION BY user_id)
A running or partitioned row count.
COUNT(*) OVER (PARTITION BY user_id)
The lowest value across the window or partition.
MIN(total) OVER (PARTITION BY user_id)
The highest value across the window or partition.
MAX(total) OVER (PARTITION BY user_id)
SUM with ORDER BY and a frame open at the start; each row sums everything up to itself.
SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
AVG over a frame of n rows before and after the current row.
AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
First, last, and percentiles
The first value in the window's ordering.
FIRST_VALUE(total) OVER (ORDER BY total DESC)
The last value in the frame; without UNBOUNDED FOLLOWING the frame stops at the current row.
LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
The value at the nth row of the window's ordering.
NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
The relative rank of a row, from 0 to 1.
PERCENT_RANK() OVER (ORDER BY total)
The share of rows at or before the current row, from 1/n up to 1.
CUME_DIST() OVER (ORDER BY total)
The OVER clause
Defines the window every function reads.
<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
Restarts the window for each group of rows.
PARTITION BY user_id
Sequences rows inside each partition.
ORDER BY created_at
Bounds the sliding frame, e.g. preceding to following rows.
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
Bounds the frame by values, not row positions; CURRENT ROW includes tied peers, and this is the default frame under ORDER BY.
RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
Bounds the frame by groups of tied rows from the ORDER BY.
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
Removes the current row from the frame; EXCLUDE TIES removes its peers instead.
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
Names a window once per query so several OVER clauses can reuse it.
WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
Narrows the rows a window aggregate reads, before the window is applied.
SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)