← databases book ⊞ All topics

Window Functions — RANK, ROW_NUMBER, LAG/LEAD, Partitions & Frames

A window function computes an aggregate — a sum, a rank, a running total — without collapsing the rows it aggregated over. Where GROUP BY turns many rows into one, a window function stamps the group's answer onto every row and lets each row keep its own identity. That single difference is why SQL can answer "this transaction, and also its merchant's running total" in one pass.

Intermediate sql window-function over-clause partition-by rows-vs-range interview-priority Created 2026-08-24 · Updated 2026-08-31

Key Components

Window function
A calculation over a set of related rows that leaves every row in the output — one output row per input row, not one per group.
PARTITION BY
Splits rows into groups for the window function to look at, the same way GROUP BY does — but without merging the group into one row.
Window ORDER BY
The ORDER BY written inside OVER(...). It decides what "before this row" means for ranking, LAG, and running totals. It is separate from the query's own trailing ORDER BY, which only decides print order.
Frame clause
The ROWS/RANGE/GROUPS BETWEEN ... part of OVER(...). It picks which slice of the ordered partition a given row's calculation actually sees — the whole partition, a running prefix, or a fixed-size neighbourhood.
Peer group
Rows the window's ORDER BY cannot tell apart — they are tied on the ordering key. Ranking functions and the default frame both treat a peer group as a single unit, which is the root of most window-function surprises.

Concrete Example

Before window functions, seeing a row next to a group total meant computing the total separately and joining it back:

-- The old way: aggregate in a subquery, join back to recover the rows
SELECT t.txn_id, t.amount, m.merchant_total
FROM transactions t
JOIN (SELECT merchant_id, SUM(amount) AS merchant_total
      FROM transactions GROUP BY merchant_id) m USING (merchant_id);

A window function says the same thing in one pass, one clause, no join:

SELECT txn_id, merchant_id, amount,
       SUM(amount) OVER (PARTITION BY merchant_id) AS merchant_total
FROM transactions;

Run against four rows across two merchants, the result keeps every row and adds one column:

txn_idmerchant_idamountmerchant_total
15014090
25015090
39002045
49002545

Four rows in, four rows out. Each row still shows its own txn_id and amount, and merchant_total is the same number for every row that shares a merchant_id — the partition's answer, stamped onto each of its rows.

The single most common production use of this idea is top-N per group. Rank inside a partition with ROW_NUMBER, then keep only the top rows:

WITH ranked AS (
    SELECT merchant_id, txn_id, amount,
           ROW_NUMBER() OVER (PARTITION BY merchant_id
                              ORDER BY amount DESC, txn_id) AS rn
    FROM transactions
    WHERE status = 'SUCCESS'          -- filter first: this step runs before ranking
)
SELECT * FROM ranked WHERE rn <= 3;

Filtering status = 'SUCCESS' inside the CTE shrinks the input before ranking runs, and it changes the question being asked: ranks are computed among successful transactions only. Filtering outside would rank everything first and discard afterward — slower, and a different answer.

Visual Model

Think of a window function as a clerk who walks down a sorted stack of rows with a small movable window over part of the stack. At each row, the clerk looks only through the window, computes one number, and writes it on that row before moving on. The two hardest questions are: what happens when two rows in the window tie, and how big is the window. Step through both below — same idea, two different tables.

Step 1 of 7
Table A — ORDER BY amount DESC (ties: 90, 90) amount ROW_NUMBER() RANK() DENSE_RANK() 100 90 90 80 1 2 3 4 1 2 2 4 ← skips 3 1 2 2 3 ← no gap Table B — running total, ORDER BY day (peers: two rows on d2) day amount RANGE total (default) ROWS total d1 d2 d2 d3 100 50 30 20 100 180 180 200 ← stalls: both peers share one total 100 150 180 200 All four ranking functions and RANGE/GROUPS frames are defined in terms of the peer group.

Both tables show the same rule from two angles: a peer group is one unit, and every function or frame has to decide what "one unit" means. ROW_NUMBER ignores peers entirely; RANK/DENSE_RANK share a rank across them but disagree on whether to leave a gap; and the default RANGE frame shares one running total across them, while ROWS refuses to.

Deeper — Edge Cases & Gotchas

Why window functions can't appear in WHERE

SQL evaluates a query in a fixed order, and window functions slot in late — after WHERE and GROUP BY/HAVING, before DISTINCT and the final ORDER BY:

3. WHERE -> filter rows 4. GROUP BY -> collapse into groups HAVING -> filter groups 4.5 WINDOW FUNCTIONS <- computed here 5. SELECT -> output expressions 6. DISTINCT 7. UNION 8. ORDER BY 9. LIMIT

By the time step 4.5 runs, WHERE (step 3) has already finished. There is no way for WHERE to filter on a value that does not exist yet.

Anti-pattern: filtering directly on a window function in WHERE. 
-- ERROR:  window functions are not allowed in WHERE
SELECT merchant_id, txn_id,
       ROW_NUMBER() OVER (PARTITION BY merchant_id ORDER BY amount DESC) AS rn
FROM transactions
WHERE rn <= 3;

This fails for a structural reason, not a syntax slip. The fix is to wrap the query: a subquery or CTE finishes one query's evaluation entirely, so in the outer query the window result is an ordinary column with no window nature left, and an ordinary WHERE can filter it.

WITH ranked AS (
    SELECT merchant_id, txn_id,
           ROW_NUMBER() OVER (PARTITION BY merchant_id ORDER BY amount DESC) AS rn
    FROM transactions
)
SELECT * FROM ranked WHERE rn <= 3;

One exception: the query's own trailing ORDER BY is allowed to reference a window function, because it runs at step 8 — after window functions have already been computed.

LAST_VALUE returns the current row, not the last row

The default frame is RANGE UNBOUNDED PRECEDING, meaning "from the partition start through the current row's last peer." FIRST_VALUE looks correct only because the frame's start happens to be the partition start. LAST_VALUE is not so lucky — the frame's end is the current row, so "the last row of the frame" is the current row itself.

-- WRONG: returns the CURRENT row's own amount on every row
LAST_VALUE(amount) OVER (PARTITION BY merchant_id ORDER BY created_at)

-- FIX: open the frame at both ends
LAST_VALUE(amount) OVER (PARTITION BY merchant_id ORDER BY created_at
                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- OR sidestep it entirely
FIRST_VALUE(amount) OVER (PARTITION BY merchant_id ORDER BY created_at DESC)

Deciding ROWS vs RANGE

The rule of thumb that resolves almost every case: "the last 7 rows" is ROWS; "the last 7 days" is RANGE. They differ whenever row density varies — a 7-row average and a 7-day average are the same query only on days with exactly one row. If the ordering column can contain duplicates and the goal is strict row-by-row accumulation, write ROWS explicitly rather than relying on the default.

The idea behind sessionization and gaps-and-islands

GROUP BY can only group on equality of a value — it cannot see a relationship between rows, such as "adjacent" or "no gap since the previous row." The fix is always the same move: compute that relationship into a per-row value that stays constant across the rows that belong together, then group by the constant.

ProblemThe relationshipThe constant it becomes
Sessionize on an idle gap"no gap > 30 min since the previous row"running SUM of a 0/1 boundary flag from LAG
Runs of consecutive dates"this date is the previous date + 1"date - ROW_NUMBER() OVER (ORDER BY date)

A running SUM over a 0/1 flag is a counter that only increments at boundaries, so every row between two boundaries carries the same total — that total is the session id. For consecutive dates, the date and the row number both increase by exactly 1 per row, so their difference is constant within a run and jumps only at a break.

Cost: a window function needs its partition sorted

EXPLAIN shows a WindowAgg node with a Sort or an Index Scan directly beneath it. An index matching (partition_key, order_key) can supply the rows pre-sorted and remove the Sort entirely — the same leftmost-prefix logic as any composite index. Several functions sharing one named window cost one sort; each distinct window costs its own.

Test Yourself

You need exactly the top 3 highest-paid employees per department — never more than 3, even if salaries tie. Which function do you rank with?

SUM(amount) OVER (PARTITION BY merchant_id ORDER BY created_at) is written with no explicit frame clause. Two rows share the same created_at value. What do those two rows see?

You write LAST_VALUE(amount) OVER (PARTITION BY merchant_id ORDER BY created_at) with no explicit frame, expecting the merchant's most recent transaction amount stamped on every row. What actually happens, and why?