Window Functions

ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running totals and moving averages: calculations across related rows that keep every row in the result.

Overview

A window function computes a value for each row from a set of related rows, called its window. Unlike GROUP BY, it does not collapse the rows: every input row stays in the result and gains a new column. That is why window functions answer questions like 'rank within each department', 'change since yesterday' and 'running total', which are among the most common SQL interview questions.

The sample tables

Six employees, with a salary tie in Eng, and five sales, with two on the same day and no sale on January 3. The tie and the gap are what make the functions below differ. All the SQL on this page runs on PostgreSQL.

The parts of OVER

Every window function is followed by OVER (...), which describes the window in three optional parts.

PARTITION BY dept — which rows belong together? — all rows form one partition
ORDER BY salary DESC — in what order, inside a partition? — no order; every row is a peer of every other
frame: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — which rows of the partition feed this row's value? — see the frame section below

Ana — Eng — 150000 — 120000 — 30000
Ben — Eng — 120000 — 120000 — 0
Cai — Eng — 120000 — 120000 — 0
Dan — Eng — 90000 — 120000 — -30000
Dee — Sales — 100000 — 90000 — 10000
Eli — Sales — 80000 — 90000 — -10000

Window functions run after WHERE, GROUP BY and HAVING, and are allowed only in the SELECT list and ORDER BY. So WHERE rn <= 2 on a window column fails with 'column "rn" does not exist'. Compute the window in a subquery or CTE and filter outside it.

Ranking: ROW_NUMBER, RANK, DENSE_RANK

The three ranking functions differ only in how they treat ties. Rows that sort equal under the window's ORDER BY are called peers.

ROW_NUMBER() — peers get different numbers, in no fixed order unless you add a tie-breaker — 1, 2, 3, 4
RANK() — peers share a rank; the next rank skips ahead — 1, 2, 2, 4
DENSE_RANK() — peers share a rank; no gaps — 1, 2, 2, 3

Ana — 150000 — 1 — 1 — 1
Ben — 120000 — 2 — 2 — 2
Cai — 120000 — 3 — 2 — 2
Dan — 90000 — 4 — 4 — 3

The wording of the question decides. 'One row per group' (deduplication, latest record) needs ROW_NUMBER. 'The third highest distinct salary' needs DENSE_RANK. 'Olympic-style places' needs RANK.

The classic top-N per group puts the rank in a subquery and filters outside it.

Ana — Eng — 150000 — 1
Ben — Eng — 120000 — 2
Cai — Eng — 120000 — 2
Dee — Sales — 100000 — 1
Eli — Sales — 80000 — 2

With DENSE_RANK, both Ben and Cai count as second. With ROW_NUMBER exactly two Eng rows would come back, and which of Ben or Cai depends on the tie-breaker.

LAG and LEAD: the row before and after

LAG(x) reads x from the previous row of the partition, and LEAD(x) from the next. Both take an optional offset (default 1) and a default value for when there is no such row (otherwise NULL). They answer 'change since the last period'.

2024-01-01 — 100 — NULL — NULL
2024-01-02 — 80 — 100 — -20
2024-01-04 — 70 — 80 — -10
2024-01-05 — 20 — 70 — -50

Note the row for January 4. LAG returns the previous row, which is January 2, not January 3. If the question means the previous calendar day, join on day - 1 or fill in the missing dates first.

Frames: running totals and moving averages

Aggregates such as SUM and AVG become running or sliding values when the window has an ORDER BY. The frame decides which rows go into each row's value.

With ORDER BY and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE includes all peers of the current row, so rows that tie on the sort key get the same running total. ROWS stops at the current row itself.

1 — 2024-01-01 — 100 — 100 — 100
2 — 2024-01-02 — 50 — 180 — 150
3 — 2024-01-02 — 30 — 180 — 180
4 — 2024-01-04 — 70 — 250 — 250
5 — 2024-01-05 — 20 — 270 — 270

For a moving window, ROWS 2 PRECEDING means the last three rows, while RANGE INTERVAL '2 days' PRECEDING means the last three calendar days. They differ as soon as there are gaps or several rows per day.

1 — 2024-01-01 — 100 — 100 — 100
2 — 2024-01-02 — 50 — 150 — 180
3 — 2024-01-02 — 30 — 180 — 180
4 — 2024-01-04 — 70 — 150 — 150
5 — 2024-01-05 — 20 — 120 — 90

On January 5, the last three rows are 30, 70 and 20, but the last three days (January 3 to 5) hold only 70 and 20. A RANGE frame with an offset needs exactly one ORDER BY column.

The default frame also explains a common surprise: LAST_VALUE returns the current row (or its last tie), not the last row of the partition, because the default frame ends at the current row's last peer. Give it a full frame.

Ana — 150000 — Ana — Eli
Ben — 120000 — Ben — Eli
Cai — 120000 — Cai — Eli
Dee — 100000 — Dee — Eli
Dan — 90000 — Dan — Eli
Eli — 80000 — Eli — Eli

Other useful window functions

NTILE(n) — bucket 1 to n, as equal in size as possible — quartiles
PERCENT_RANK() — (rank - 1) / (rows - 1), from 0 to 1 — percentile of a score
CUME_DIST() — share of rows at or before this one, counting its ties — 'top 10 percent'
FIRST_VALUE(x), NTH_VALUE(x, n) — x from the first or nth row of the frame — best seller per month
SUM(COUNT(*)) OVER () — a total over the grouped result — percent of total

A window function can wrap an aggregate, because windows run after GROUP BY. This computes each department's share in one pass.

Eng — 4 — 66.7
Sales — 2 — 33.3

When several functions share one window, name it once with the WINDOW clause: ... OVER w FROM employees WINDOW w AS (PARTITION BY dept ORDER BY salary DESC).

What you will be able to do

Explain how a window differs from GROUP BY, and where window functions may appear.
Choose between ROW_NUMBER, RANK and DENSE_RANK from the wording of a question.
Write top-N per group, change since the previous row, running totals and moving averages.
Predict what the default frame does with ties, and when to write ROWS or RANGE explicitly.

When to use it

Ranking rows inside each group while keeping every row (top N per category).
Keeping one row per key, such as the latest record per user (ROW_NUMBER = 1).
Comparing a row with the one before or after it (LAG, LEAD).
Running totals, cumulative counts and moving averages.
Showing a row next to its group total or average, or its percent of the total.
Finding streaks and consecutive runs (gaps and islands).

Interview questions

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
How do you compute a running total and a 7-day moving average?
How do you find streaks of consecutive days?

More Databases & SQL topics

Start free on Cariara · Coding problems · System design studio