Window Functions

7 pages · ~5 min✓ Reviewed

Builds on Subqueries and CTEs. Next up: Indexes and Query Plans.

SQLPage 1

Window Functions Mental Model

1. What a Window Function Is

Computes a value per row over a set of related rows
Rows are not collapsed; GROUP BY returns one row per group
OVER() turns an aggregate or ranking function into a window function
Empty OVER() = window is the whole result set

2. PARTITION BY and ORDER BY

Inside OVER	Effect
`PARTITION BY`	Independent groups; function restarts in each
`ORDER BY`	Row order in the partition
`ORDER BY` + aggregate	Default frame becomes running

sql

SELECT cust, d, amount,
  SUM(amount) OVER (
    PARTITION BY cust ORDER BY d
  ) AS running
FROM orders;

running total restarts per customer

3. Where It Runs

WHERE
GROUP BY
HAVING
Window
ORDER BY, LIMIT

Not in WHERE or HAVING

Windows run after them, so filter a window result in an outer query or CTE.

SQLPage 2

Ranking: ROW_NUMBER, RANK, DENSE_RANK

1. Three Ranking Functions

Function	Scores 90, 80, 80, 70	Ties
`ROW_NUMBER()`	1, 2, 3, 4	unique, never tied
`RANK()`	1, 2, 2, 4	same number, gap after
`DENSE_RANK()`	1, 2, 2, 3	same number, no gap

ROW_NUMBER breaks ties in arbitrary order
Order becomes stable only with a tiebreaker column

2. Rank Within a Group

PARTITION BY restarts numbering in each department
ORDER BY salary DESC gives the top pay number 1

sql

SELECT name, dept, salary,
  ROW_NUMBER() OVER (
    PARTITION BY dept
    ORDER BY salary DESC, id
  ) AS rn
FROM employees;

employees numbered by pay inside each dept

3. Gotchas

Ranking with no ORDER BY in OVER is meaningless or an error
Tied rows get arbitrary ROW_NUMBERs, so results can change between runs

Always add a tiebreaker

End the OVER ordering with a unique column such as id so ROW_NUMBER is deterministic.

SQLPage 3

LAG and LEAD

1. Looking at Other Rows

LAG(col, n, default) reads n rows before the current row
LEAD(col, n, default) reads n rows after
Defaults: n = 1, default = NULL
First row of a partition: LAG gives NULL unless a default is set

2. Month-over-Month Change

sql

SELECT month, amount,
  amount - LAG(amount) OVER (ORDER BY month) AS change
FROM sales;

month	amount	change
Jan	100	NULL
Feb	120	20
Mar	90	-30

current row minus previous row

Add PARTITION BY so rows never read across groups
One sequence per customer or per product

sql

LAG(amount, 1, 0) OVER (
  PARTITION BY customer_id ORDER BY month)

default 0 replaces the edge NULL

3. Gotchas

No ORDER BY in OVER: previous row is meaningless
Edge-row NULL breaks arithmetic: amount - NULL is NULL
Fix with the default argument or COALESCE(LAG(...), 0)

Remember

LAG looks back, LEAD looks ahead. Always set ORDER BY, and handle the edge-row NULL.

SQLPage 4

Running Totals and Frames

1. Frame Clause

ROWS | RANGE | GROUPS BETWEEN start AND end
Bounds: UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING

With ORDER BY: default is RANGE UNBOUNDED PRECEDING to CURRENT ROW
No ORDER BY: default is the whole partition

2. ROWS vs RANGE

	ROWS	RANGE
Counts	physical rows	value peers
Tied dates	different totals	same total

3. Patterns

sql

SUM(x) OVER (PARTITION BY g ORDER BY d
  ROWS BETWEEN UNBOUNDED PRECEDING
  AND CURRENT ROW)  -- running total
AVG(x) OVER (ORDER BY d
  ROWS BETWEEN 6 PRECEDING
  AND CURRENT ROW)  -- last 7 rows

LAST_VALUE gotcha

Default frame ends at the current row's peer, not the partition end. Add UNBOUNDED FOLLOWING.

SQLPage 5

Top-N per Group and Common Mistakes

1. The Top-N Pattern

Rank in a CTE or subquery, then filter WHERE rn <= N in the outer query
Window functions can't be filtered in the same SELECT's WHERE, so wrap them first

sql

WITH r AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY g ORDER BY v DESC) rn
  FROM t)
SELECT * FROM r WHERE rn <= 3;

top 3 per group

2. Ties and Latest Row

Function	Rows per group
`ROW_NUMBER`	exactly N
`RANK` / `DENSE_RANK`	keeps ties, may exceed N

Latest row per group: ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC)
Then keep rn = 1

3. Common Mistakes

ORDER BY inside OVER does not sort the output
Add a final ORDER BY if you need ordered results

Rank first, filter after

The rank column exists only once the CTE has run, so WHERE rn <= N always goes in the outer query.

SQLPage 6

Window Functions Cheat Sheet

1. Syntax and Ranking

func() OVER (PARTITION BY cols ORDER BY cols frame)
Ranking needs ORDER BY inside OVER

Function	On ties	Output
`ROW_NUMBER`	unique	1,2,3,4
`RANK`	gaps	1,2,2,4
`DENSE_RANK`	no gaps	1,2,2,3

2. Offsets and Running Total

LAG(col,n,default) looks back
LEAD(col,n,default) looks ahead
Edge rows give NULL unless a default is set

sql

SUM(x) OVER (ORDER BY d
  ROWS BETWEEN UNBOUNDED PRECEDING
  AND CURRENT ROW)

running total

3. Top-N per Group

ROW_NUMBER in a CTE, then filter rn <= N

sql

WITH r AS (SELECT *, ROW_NUMBER()
  OVER (PARTITION BY g ORDER BY v DESC) rn
  FROM t)
SELECT * FROM r WHERE rn <= 3;

Interview Point

On ties ROW_NUMBER stays unique, RANK skips, DENSE_RANK doesn't. ROWS counts rows; RANGE includes peers.

SQLPage 7

Check yourself

Salaries 90, 80, 80, 70 sorted DESC. What do ROW_NUMBER, RANK and DENSE_RANK return?

ROW_NUMBER: 1, 2, 3, 4 (tie order arbitrary)
RANK: 1, 2, 2, 4 (gap after the tie)
DENSE_RANK: 1, 2, 2, 3 (no gap)

Why does WHERE rn <= 3 fail when rn is a ROW_NUMBER in the same SELECT?

WHERE runs before window functions, so rn does not exist yet
Compute rn in a CTE, then filter in the outer query

Amounts by month are 100, 130, 120. What does amount - LAG(amount) OVER (ORDER BY month) return?

NULL, 30, -10
The first row has no previous row, so LAG is NULL
Pass a default or use COALESCE to avoid the NULL

Dates 1, 2, 2, 3 with amounts 10, 20, 30, 40. What does SUM(amount) OVER (ORDER BY d) return?

10, 60, 60, 100
Default frame is RANGE: tied dates are peers and share one total
With ROWS it would be 10, 30, 60, 100