02 — Window Functions

A window function is a SQL calculation that looks at a group of related rows and attaches an answer to every one of them, instead of folding them into a single summary row.

“Rank each player inside their own team.” “This month against last month.” “The total so far.” Each of those is one window function, and none of them needs a self-join.

Four things separate knowing that definition from being able to use it:

• what a window function computes, and — just as importantly — when the database computes it;
• what each of the three parts inside OVER (...) does: PARTITION BY, ORDER BY, and the frame;
• which defaults silently return a wrong answer instead of an error;
• the six patterns interviewers ask for: ranking, period-over-period, running totals, sessionization, de-duplication, and cohorts.

By the end you should be able to write those queries from memory, name the assumption each one is making, and explain to a colleague why WHERE rn = 1 cannot work.

This chapter stands on its own. Every idea it needs is stated here. The links to chapter 01 are for extra depth on a neighbouring topic, never a prerequisite.

Most people learn window functions as a bag of tricks: ROW_NUMBER to de-duplicate (keep one row per key, drop the copies), LAG for growth rates, SUM() OVER for running totals. Then an interviewer asks why WHERE rn = 1 fails, or why a running total repeated the same value twice, and the bag falls apart.

One fact resolves nearly all of it, and it goes first.

Six words this chapter uses constantly

Each gets a fuller definition where it first does real work.

1. What a window function is, and — more importantly — when it runs

Two things pin the concept down: the contrast with an ordinary aggregate, and the point in query execution at which windows are evaluated — the single fact from which most window-function surprises follow.

Aggregate versus window: eight rows in, eight rows out

An aggregate is a function like SUM, AVG or COUNT used with GROUP BY. It collapses N input rows into 1 output row per group.

A window function computes a value over a set of related rows and attaches that value to every row, collapsing nothing. Same inputs, same arithmetic. The only difference is whether the input rows survive.

Here is the input both queries below read. Eight players, three on Blue and five on Red:

scores
player team  points
Ada    Red      90
Bo     Red      90
Cy     Red      80
Di     Red      80
Ed     Red      70
Fay    Blue     95
Gus    Blue     85
Hal    Blue     85

Now the same arithmetic — the average of points per team — written twice. The only textual difference is the word OVER in the second one.

-- collapsing: one output row per team
SELECT team, AVG(points) FROM scores GROUP BY team;

-- not collapsing: every input row kept, one extra column
SELECT team, player, points,
       AVG(points) OVER (PARTITION BY team) FROM scores;

What came back from the first query. Two rows — one average per team. The player names are gone, because GROUP BY threw them away.

team  avg
Blue  88.33
Red   82.00

What came back from the second. Eight rows: the original ones, untouched, each with one extra column holding its own team’s average.

team  player  points  avg
Blue  Fay        95   88.33
Blue  Gus        85   88.33
Blue  Hal        85   88.33
Red   Ada        90   82.00
Red   Bo         90   82.00
Red   Cy         80   82.00
Red   Di         80   82.00
Red   Ed         70   82.00

Eight rows in, eight rows out, one column wider. That is the whole shape of the thing. (Both averages are shown rounded to two places; raw AVG on an int column returns full precision, 88.3333....)

Read the second query aloud to fix it in memory: take AVG(points), but instead of collapsing, compute it OVER a window — here, the rows that share this row’s team. The keyword OVER is what makes the difference.

The logical processing order

Now the timing, which is where the confusion actually lives.

SQL is not executed in the order you type it. The database follows a fixed logical processing order — the sequence in which it evaluates the clauses of a query, regardless of the order you wrote them. Every clause is numbered by where it sits in that pipeline:

flowchart TD
    F["1 · FROM / JOIN"] --> W
    W["2 · WHERE"] --> G
    G["3 · GROUP BY"] --> H
    H["4 · HAVING"] --> WIN
    WIN["5 · WINDOW FUNCTIONS<br/>input = the rows that survived 1-4"] --> S
    S["6 · SELECT list"] --> D
    D["7 · DISTINCT"] --> O
    O["8 · ORDER BY"] --> L
    L["9 · LIMIT / OFFSET"]

    WIN -.->|"cannot look back"| W
    WIN -.->|"cannot look back"| H

    style WIN fill:#bc6c25,color:#fff
    style W fill:#1d3557,color:#fff
    style H fill:#1d3557,color:#fff

Read the diagram top to bottom. It has three parts.

Steps 1 to 4 assemble and filter rows. FROM/JOIN produces the raw row set, WHERE throws individual rows away, GROUP BY folds the survivors into groups, and HAVING throws whole groups away.

Step 5 is the window step, and the label on the orange box spells out what it consumes: the rows that survived steps 1 to 4, nothing more and nothing less.

Steps 6 to 9 shape the output. The SELECT list evaluates the expressions you asked for, DISTINCT removes duplicate output rows, ORDER BY sorts, and LIMIT / OFFSET slices off the page you asked for.

The two dotted arrows carry the consequence: a value computed at step 5 cannot be seen by steps 2 and 4, because those already finished.

Window functions run after WHERE, GROUP BY, and HAVING, and before DISTINCT, ORDER BY, and LIMIT.

Six consequences of that one sentence

Every row of this table is derived from the diagram above — nothing else is needed to answer any of these.

The fix: compute the window inside, filter outside

The fix for the first two rows of that table is always the same shape: compute the window in an inner query, filter in an outer one.

The inner query below is the WITH ranked AS (...) block — a common table expression (CTE), a named temporary result set you can then select from as if it were a table. The first query is shown only so you can see the error it raises; the second is the one to write.

-- ERROR:  window functions are not allowed in WHERE
SELECT player, team, points,
       RANK() OVER (PARTITION BY team ORDER BY points DESC) AS rk
FROM scores
WHERE RANK() OVER (PARTITION BY team ORDER BY points DESC) <= 2;

-- correct: one extra level of nesting
WITH ranked AS (
    SELECT player, team, points,
           RANK() OVER (PARTITION BY team ORDER BY points DESC) AS rk
    FROM scores
)
SELECT * FROM ranked WHERE rk <= 2;

What came back. The first query never runs — PostgreSQL rejects it with ERROR: window functions are not allowed in WHERE.

The second returns five rows, each still carrying its rk column: Ada and Bo from Red (tied at rank 1), and Fay, Gus and Hal from Blue (Fay at 1, then Gus and Hal tied at 2). Five rows from a query that asked for “the top 2 per team” is not a bug — it is what RANK means, and Top n per group done properly is about exactly that.

Why the nesting works. It runs the whole nine-step pipeline twice. The inner query finishes through step 5 and produces rk. The outer query then starts again at step 1 with those rows as its input, so by the time its WHERE runs at step 2, rk is an ordinary column like any other.

That extra level is not overhead you can optimize away. It is the pipeline made explicit.

Dialect note. A dialect is one vendor’s variant of SQL. Snowflake, BigQuery, Databricks, and DuckDB accept QUALIFY rk <= 2, a clause that filters on window results directly and is defined to mean exactly this nesting. PostgreSQL, MySQL, SQLite, and SQL Server have no QUALIFY, so there you write the CTE.

The sentence to say in an interview: “Window functions are evaluated after WHERE/GROUP BY/HAVING and before ORDER BY/LIMIT, so their results don’t exist yet when WHERE runs — which is why filtering on a window result requires a subquery or CTE, and why LIMIT doesn’t reduce the work.” Everything else about windows follows from that.

2. The schema, and the anatomy of OVER

Every example from here on runs against a handful of small tables. Once those are on the page, the OVER (...) clause comes apart into three components — each with a default that applies when you leave it out.

The tables

The dialect throughout is PostgreSQL, and dialect differences are flagged where they matter. Three small tables carry most of the chapter:

CREATE TABLE scores  (player text, team text, points int);
CREATE TABLE monthly_revenue (month date, region text, revenue int);
CREATE TABLE sales   (sale_date date, amount int);

Four more appear in the later pattern sections. Their columns, abbreviated to what this chapter actually reads:

CREATE TABLE customers  (customer_id int, name text, region text, signup_date date);
CREATE TABLE orders     (order_id int, customer_id int, order_ts timestamptz, amount_cents int);
CREATE TABLE events     (event_id bigint, user_id int, event_ts timestamptz, kind text);
CREATE TABLE logins     (user_id int, login_date date);

customers, orders, and events are the same tables as in The schema every example runs against; you do not need to read that to follow this.

Here are the rows in the three main tables. Every result below can be checked against them by hand.

scores                          monthly_revenue (region='US')     sales
player team  points             month       revenue               sale_date   amount
Ada    Red      90              2025-01-01     1000               2025-03-01     100
Bo     Red      90              2025-02-01     1200               2025-03-01     200
Cy     Red      80              2025-03-01     1100               2025-03-02      50
Di     Red      80              2025-04-01     1500               2025-03-03     300
Ed     Red      70              2025-05-01     1650               2025-03-03     400
Fay    Blue     95              2025-06-01     1400
Gus    Blue     85
Hal    Blue     85

Two properties of that data are deliberate, and both will matter later.

1. scores contains tied point values: Ada and Bo both have 90, Gus and Hal both have 85.
2. sales contains two rows on the same date, twice over: on 2025-03-01 and on 2025-03-03.

Ties and duplicate ordering keys are exactly where window functions start returning answers you did not intend.

Inside OVER: three parts, three defaults

Every window function is written as a function call followed by OVER and a parenthesized window definition. The definition has three optional parts, and each one has a default that applies when you omit it:

flowchart TD
    OV["f(args) OVER ( ... )"] --> P
    P["PARTITION BY team<br/>a GROUP BY that does not collapse<br/>omit -> one partition = all rows"] --> OB
    OB["ORDER BY points DESC<br/>orders rows WITHIN the partition<br/>omit -> no order, frame = whole partition"] --> FR
    FR["ROWS/RANGE/GROUPS BETWEEN a AND b<br/>which rows the function sees<br/>with ORDER BY, omit -> RANGE<br/>UNBOUNDED PRECEDING AND CURRENT ROW"] --> R["one value per input row"]

    style P fill:#1d3557,color:#fff
    style OB fill:#2d6a4f,color:#fff
    style FR fill:#9d0208,color:#fff

Walk the diagram from the top. f(args) OVER ( ... ) is the general form: any function f with its arguments, then the window definition in parentheses. Then one box per part.

PARTITION BY splits the rows into independent groups and restarts the calculation in each one. It is a GROUP BY that does not collapse: same grouping, but the rows come back out. Omit it entirely and you get one partition holding every row — which is silent, never an error, and is the first of the three assumptions below.

ORDER BY sorts the rows within each partition. This is a different clause from the query’s final ORDER BY, even though it is spelled the same; this one lives inside the parentheses and only affects the window. Omit it and the rows have no order at all.

The frame is the third part, spelled ROWS, RANGE or GROUPS followed by BETWEEN a AND b. The frame is the slice of the current partition that the function actually looks at for the current row, and it can be a different slice for every row.

That third one needs two pieces of vocabulary before it means anything:

• UNBOUNDED PRECEDING means “start at the first row of the partition”.
• CURRENT ROW means, roughly, “stop here” — and the word roughly is doing real work, which Rows vs range vs groups and the default that causes the above unpacks.

So the default frame, which applies when you write an ORDER BY and no frame clause, is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — “everything from the start of the partition up to here”.

Whatever the definition, the output shape is the same: one value per input row.

Three clauses, three defaults, and the frame default is the one that bites (Rows vs range vs groups and the default that causes the above).

PARTITION BY on its own

Used alone, PARTITION BY gives you the “compare a row to its group” pattern with no self-join. The query below computes three per-team numbers and attaches all three to every player. The trick is that PARTITION BY team appears three times, and each time it means the same thing: restart within this row’s team.

SELECT player, team, points,
       ROUND(AVG(points) OVER (PARTITION BY team), 2)          AS team_avg,
       points - AVG(points) OVER (PARTITION BY team)           AS vs_avg,
       COUNT(*) OVER (PARTITION BY team)                       AS team_size
FROM scores ORDER BY team, points DESC;

player team  points  team_avg  vs_avg   team_size
Fay    Blue     95     88.33    +6.67       3        (265/3 = 88.33)
Gus    Blue     85     88.33    -3.33       3
Hal    Blue     85     88.33    -3.33       3
Ada    Red      90     82.00    +8.00       5        (410/5 = 82.00)
Bo     Red      90     82.00    +8.00       5
Cy     Red      80     82.00    -2.00       5
Di     Red      80     82.00    -2.00       5
Ed     Red      70     82.00   -12.00       5

What came back. Eight rows, three columns wider. Every Blue row carries the Blue average and every Red row carries the Red average, and the individual points column is still sitting there beside it. That last part is what a GROUP BY could not have given you without joining the grouped result back to the detail rows.

Notice that team_avg is constant down each team. Here is why, and it matters more than it looks.

With no ORDER BY inside OVER, every row in the partition is a peer of every other. A peer is a row the window’s ORDER BY cannot tell apart from the current one — and with no ORDER BY at all, nothing can be told apart from anything. So the frame is the whole partition, and the average comes out flat.

Add an ORDER BY and that same AVG(points) OVER (...) silently becomes a running average: it recomputes over only the rows up to and including the current one (Rows vs range vs groups and the default that causes the above). The presence or absence of ORDER BY inside OVER changes what an aggregate window means. Nothing about the function name warns you.

Two structural rules to remember:

• Window functions are legal only in the SELECT list and in the query’s final ORDER BY. Nowhere else.
• They cannot be nested. RANK() OVER (ORDER BY SUM(x) OVER ()) is a syntax error. Layer CTEs instead.

The three assumptions to state out loud

Three assumptions are baked into every window query, and all three fail quietly rather than loudly. Each one is developed in the section named in the last column. They belong together here because “which of these three am I assuming?” is the question that catches most window bugs before they ship.

Do not try to absorb the whole table now — come back to it after Rows vs range vs groups and the default that causes the above, where the third row is demonstrated rather than asserted.

A total order is an ordering in which every pair of rows is distinguishable — no ties. ORDER BY points DESC over the scores table is not a total order, because Ada and Bo both have 90. Adding a column that is unique per row, such as ORDER BY points DESC, player, makes it one.

3. The ranking family: ROW_NUMBER, RANK, DENSE_RANK

Six functions assign a position to each row: three ranking functions, plus three that express position as a fraction or a bucket. The only difference between the first three is how they treat ties.

All of them number rows within a partition in ORDER BY sequence. They differ only in how they treat peers — rows the ORDER BY cannot distinguish, which in the scores table means Ada and Bo (both 90) and Gus and Hal (both 85).

The first three differ in exactly two ways: what a tied pair gets, and what the row after a tied pair gets.

All six at once

The query below computes all six functions over the same window so you can compare them column by column.

It uses a named window: the WINDOW w AS (...) clause at the bottom defines a window specification once, and every function then refers to it as OVER w. That is less to read, and — as Performance count the sorts shows — it makes it visible at a glance that these really are the same window. PostgreSQL, MySQL 8, SQLite, and SQL Server 2022 accept the WINDOW clause; older SQL Server and some cloud engines do not, and there you repeat the definition in each OVER (...).

SELECT team, player, points,
       ROW_NUMBER()  OVER w AS rn,
       RANK()        OVER w AS rk,
       DENSE_RANK()  OVER w AS drk,
       ROUND(PERCENT_RANK() OVER w, 3) AS pct_rank,
       ROUND(CUME_DIST()    OVER w, 3) AS cume,
       NTILE(3)      OVER w AS tile
FROM scores
WINDOW w AS (PARTITION BY team ORDER BY points DESC)
ORDER BY team, points DESC;

team  player points | rn  rk  drk | pct_rank  cume  | tile
Blue  Fay       95  |  1   1   1  |  0.000    0.333 |  1
Blue  Gus       85  |  2   2   2  |  0.500    1.000 |  2
Blue  Hal       85  |  3   2   2  |  0.500    1.000 |  3
Red   Ada       90  |  1   1   1  |  0.000    0.400 |  1
Red   Bo        90  |  2   1   1  |  0.000    0.400 |  1
Red   Cy        80  |  3   3   2  |  0.500    0.800 |  2
Red   Di        80  |  4   3   2  |  0.500    0.800 |  2
Red   Ed        70  |  5   5   3  |  1.000    1.000 |  3

What came back. All eight rows, in the order the final ORDER BY asked for, with six new columns. Read down the rn, rk and drk columns in the Red block: rn runs 1,2,3,4,5 with no repeats; rk runs 1,1,3,3,5, repeating on the ties and then skipping; drk runs 1,1,2,2,3, repeating on the ties and never skipping.

Read the Red partition column by column and the definitions become mechanical:

RANK       = 1 + (number of rows strictly ahead of me)
             Cy has 2 rows ahead (both 90s) -> rank 3.  Rank 2 is never assigned.
DENSE_RANK = 1 + (number of DISTINCT values strictly ahead of me)
             Cy has 1 distinct value ahead (90) -> 2.
ROW_NUMBER = position in an arbitrary total order consistent with ORDER BY
PERCENT_RANK = (rank - 1) / (n - 1)      Cy: (3-1)/4 = 0.500
CUME_DIST    = (rows preceding or peer) / n   Cy: 4/5 = 0.800
NTILE(k)     = split n rows into k contiguous buckets, larger buckets first
               n=5, k=3 -> sizes 2,2,1

To put the last three in words: PERCENT_RANK rescales the rank onto the interval 0 to 1, so the top row is always 0 and the bottom row is always 1; CUME_DIST (cumulative distribution) answers “what fraction of the partition is at least as good as me”; and NTILE(k) chops the partition into k roughly equal buckets and returns which bucket the row landed in — NTILE(4) gives quartiles, NTILE(100) percentiles.

The failure mode: ROW_NUMBER over an ordering with ties

This is the second of the three assumptions from The three assumptions to state out loud, and it is the one that ships to production.

ORDER BY points DESC cannot separate Ada from Bo. Both have 90. So which of them gets rn = 1 is whatever the sort happened to produce — and that can differ between two runs of the same query on the same unchanged data.

Three things change it. A VACUUM (the PostgreSQL maintenance operation that reclaims dead space and can rearrange rows on disk) moves rows around. A statistics update can make the planner switch from reading the table front to back to walking an index instead. Parallel workers can return rows in a different interleaving.

A de-duplication or top-N query built on that is non-deterministic: same query, same data, different rows. It passes every test until it does not.

The fix is one clause. Always append a column that is unique per row, so the ordering has no ties left:

ROW_NUMBER() OVER (PARTITION BY team ORDER BY points DESC, player)

Now Ada always precedes Bo, on every engine and every run.

Three more properties worth knowing

All six of these functions ignore the frame clause entirely. They are defined over the whole partition, which is why nobody trips on the default frame here. Rows vs range vs groups and the default that causes the above is where that changes.

NTILE with fewer rows than buckets (n < k) leaves the trailing buckets empty rather than raising an error. Four rows into NTILE(10) gives buckets 1, 2, 3, 4 and six empty ones.

PERCENT_RANK returns 0 for a single-row partition. Its definition divides by n-1, which would be division by zero; the standard defines that degenerate case as 0 rather than an error.

4. LAG and LEAD — period over period

Two functions let a row read a different row’s value — which is how every “compared to last month” column gets written — and there are four distinct ways that comparison goes wrong.

LAG reads a row earlier in the partition’s order; LEAD reads a later one. Both take the value to fetch, how many rows away to look, and what to return when there is no such row:

LAG (expr, offset = 1, default = NULL) OVER (PARTITION BY ... ORDER BY ...)
LEAD(expr, offset = 1, default = NULL) OVER (...)

The = 1 and = NULL in that sketch are the values used when you omit those arguments. So plain LAG(revenue) means “the previous row’s revenue, or NULL if I am the first row”.

Both functions are frame-insensitive. They address rows by offset from the current one, not by frame membership, so writing a frame clause alongside them changes nothing.

The query below builds a month-over-month growth column from the six US rows of monthly_revenue. The line that carries the trick is LAG(revenue) OVER w: on each row it reaches back one row to fetch the previous month’s number, and everything else is arithmetic on top of that.

SELECT month, revenue,
       LAG(revenue) OVER w                                              AS prev,
       revenue - LAG(revenue) OVER w                                    AS delta,
       ROUND(100.0 * (revenue - LAG(revenue) OVER w)
             / NULLIF(LAG(revenue) OVER w, 0), 1)                       AS mom_pct
FROM monthly_revenue
WHERE region = 'US'
WINDOW w AS (ORDER BY month)
ORDER BY month;

month       revenue  prev  delta  mom_pct
2025-01-01    1000   NULL   NULL   NULL
2025-02-01    1200   1000   +200   +20.0      200/1000
2025-03-01    1100   1200   -100    -8.3     -100/1200
2025-04-01    1500   1100   +400   +36.4      400/1100
2025-05-01    1650   1500   +150   +10.0      150/1500
2025-06-01    1400   1650   -250   -15.2     -250/1650

What came back. Six rows, one per month, each carrying the previous month’s revenue and two derived columns. The first row’s prev is NULL because January has no earlier row, and that NULL propagates through delta and mom_pct. mom_pct is the month-over-month percentage change: the change from the previous month divided by the previous month’s value.

Four things this small query gets right. Each one is a bug you will see in real code.

1. The NULLIF guard on the denominator

NULLIF(a, b) returns NULL when a equals b, and returns a otherwise. Here it turns a zero denominator into a NULL, and dividing by NULL yields NULL rather than raising.

Without it, a month with zero revenue raises division by zero and kills the whole query. That is a hard failure, which is at least loud.

The subtler version of the same bug is integer division. Dividing one integer by another truncates to an integer in PostgreSQL, SQLite, SQL Server, and Db2, so (1200 - 1000) / 1000 in int arithmetic is 0, not 0.2. (MySQL and Oracle return 0.2 here, so the bug does not reproduce there — which is exactly why it survives a code review done by someone whose home engine is MySQL.) Multiply by 100.0 first, or cast, or your growth column is all zeros and looks like a flat business.

2. The first row is NULL, not 0

January has no earlier row, so LAG returns its default, and the default is NULL.

You could write LAG(revenue, 1, 0) to supply 0 instead. Do not. It produces a fictitious +1000 delta in January, and — if the NULLIF guard were also dropped — a division-by-zero error instead of an honest empty cell. Leave it NULL and let the reader see there is no prior period.

3. LAG(revenue, 12) is twelve rows back, not twelve months back

If any month is missing from monthly_revenue — a region with no sales in August produces no row at all — then a “year over year” column built with LAG(revenue, 12) silently compares against 13 months ago, and every row after the gap is shifted.

Two fixes. Densify the data first: join it to a calendar spine, a generated table with exactly one row per month whether or not anything happened, so absent periods become rows with zero rather than no row at all (Dates the half open interval rule). Or stop counting rows and start counting calendar distance, with a value-based RANGE frame (Rows vs range vs groups and the default that causes the above).

4. A WHERE clause upstream changes the answer

WHERE region = 'US' runs at step 2 of the pipeline in What a window function is and more importantly when it runs, so the LAG only ever sees US rows. That is correct here — comparing a US month against a European one would be meaningless.

But now imagine adding WHERE revenue > 1150. That deletes January and March. February’s “previous month” vanishes, so its growth becomes NULL. Worse, April’s “previous month” becomes February, and the query reports a growth figure computed against a month two steps back with no indication that anything is wrong.

It takes deleting an interior row to get that second failure. A filter that only removes January, such as revenue > 1050, produces the NULL and nothing worse, because every surviving row still has its true predecessor.

Filtering rows out of a window’s input silently rewrites every offset-based and cumulative column. If you need to filter the output but not the input, compute the window in a CTE and filter outside it — the same nesting as What a window function is and more importantly when it runs, applied for a different reason.

5. Running totals and the frame clause

The single most common window query is a running total — and written two ways that look equivalent, it disagrees with itself. The evidence comes first; the explanation is Rows vs range vs groups and the default that causes the above.

A running total (or cumulative sum) is a column where each row holds the sum of its own value plus every value before it, so the last row equals the grand total.

The query below computes it twice over the same five sales rows:

• running_default writes only an ORDER BY and lets the frame default.
• running_rows spells the frame out as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which reads “from the first row of the partition through this row”.

Those two ought to mean the same thing. Watch the two columns.

SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_default,
       SUM(amount) OVER (ORDER BY sale_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows
FROM sales ORDER BY sale_date, amount;

sale_date   amount | running_default | running_rows
2025-03-01     100 |       300       |     100
2025-03-01     200 |       300       |     300
2025-03-02      50 |       350       |     350
2025-03-03     300 |      1050       |     650
2025-03-03     400 |      1050       |    1050

What came back: two columns, both spelled “running total”, disagreeing on two of five rows. The two rows that disagree are the first row of each tie group.

Look at row 1. It has an amount of 100, and nothing precedes it, yet running_default already says 300. It has counted the 200 from the other row dated 2025-03-01 — a row that comes after it in the output.

Row 4 does the same thing on a larger scale: amount 300, but running_default reports 1050, having already swallowed the 400 from row 5.

Now the property that makes this dangerous: the final value is 1050 in both columns. A smoke test that checks the last row, or reconciles the running total against SUM(amount), passes. Only the intermediate values are wrong — which is exactly the part a chart plots.

6. ROWS vs RANGE vs GROUPS, and the default that causes the above

That discrepancy has a mechanical explanation: the frame clause has three modes, they disagree only when the ordering key has duplicate values, and the mode you get by default is the one that surprises people.

What a frame actually is

Take one row of the result — say row 3 of sales, the one dated 2025-03-02 with amount 50. Before the engine can compute SUM(amount) for that row, it has to decide which rows go into the sum. That set is the frame.

The frame is recomputed for every row. For a running total it grows: row 1 sees {100}, row 2 sees {100, 200}, row 3 sees {100, 200, 50}, and so on. For a 3-row moving average it slides: a fixed-width window that walks down the partition.

So there are three separate questions, and the syntax answers them in this order:

1. Which rows are even candidates? — PARTITION BY picks the partition.
2. In what order do they sit? — ORDER BY inside OVER sorts them.
3. How far back and how far forward from here do I reach? — the frame clause.

The frame clause is written <mode> BETWEEN <lower bound> AND <upper bound>, for example ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. The bounds are the endpoints. The mode decides what unit the bounds are counted in — and that is the whole of the next subsection.

The three modes

flowchart TD
    M{"frame mode"} --> R1["ROWS<br/>bounds count PHYSICAL ROWS<br/>'2 PRECEDING' = 2 rows back"]
    M --> R2["RANGE<br/>bounds count VALUES of the ORDER BY key<br/>'CURRENT ROW' = me AND ALL MY PEERS<br/>'2 PRECEDING' = key &gt;= mykey - 2"]
    M --> R3["GROUPS<br/>bounds count PEER GROUPS<br/>'2 PRECEDING' = 2 distinct key values back"]
    R2 --> D["DEFAULT when you write ORDER BY<br/>and omit the frame:<br/>RANGE BETWEEN UNBOUNDED PRECEDING<br/>AND CURRENT ROW"]

    style R2 fill:#9d0208,color:#fff
    style D fill:#9d0208,color:#fff
    style R1 fill:#2d6a4f,color:#fff

The diagram branches on the frame mode, and the branches differ in one respect only: what unit the bounds count in.

• In ROWS mode the bounds count physical rows, so 2 PRECEDING means literally two rows back in the sorted partition, regardless of what values those rows hold.
• In RANGE mode the bounds count values of the ORDER BY key. 2 PRECEDING therefore means every row whose key is at least mykey - 2, and CURRENT ROW means me and all my peers.
• In GROUPS mode the bounds count peer groups — blocks of rows sharing one key value — so 2 PRECEDING reaches back two distinct key values.

Dialect note. Every engine supports ROWS. RANGE with UNBOUNDED/CURRENT ROW bounds is universal too. GROUPS is narrower: PostgreSQL 11+ and SQLite 3.28+ have it; MySQL 8 and SQL Server do not.

And the fourth box in the diagram is the one that matters most: when you write an ORDER BY and omit the frame, you get RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Why the two running totals disagreed

Here is the sentence the whole section exists for:

In RANGE mode, CURRENT ROW does not mean the current row. It means the current row and every row tied with it on the ORDER BY key.

That single definition explains the table in Running totals and the frame clause. Here is the frame the default built for each of the five rows:

row 1 (2025-03-01, 100):  peers = rows 1,2  -> frame = {100, 200}           -> 300
row 2 (2025-03-01, 200):  peers = rows 1,2  -> frame = {100, 200}           -> 300
row 3 (2025-03-02,  50):                       frame = {100, 200, 50}       -> 350
row 4 (2025-03-03, 300):  peers = rows 4,5  -> frame = {100,200,50,300,400} -> 1050
row 5 (2025-03-03, 400):  peers = rows 4,5  -> same frame                   -> 1050

Row by row:

• Rows 1 and 2 are both dated 2025-03-01, so under RANGE they are peers. Both frames contain both amounts, and 100 + 200 = 300 for each of them.
• Row 3 has a date nobody shares, so its frame is everything up to and including itself, and the sum is the honest 350.
• Rows 4 and 5 share 2025-03-03, so both frames swallow the whole table and both report 1050.

The running_rows column in Running totals and the frame clause counts rows instead of dates, so it stops where you expect: 100, then 300, then 350, then 650, then 1050.

The default frame silently sums the whole peer group, so a “running total” over a non-unique ordering key jumps ahead of itself. With unique timestamps there are no peers, ROWS and RANGE agree, and nothing looks wrong. That is why this bug survives development on a clean dataset and appears the first day two events land in the same second.

The rule, and what each fix assumes

Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly for every running total, or make the ORDER BY key unique. Both work. Doing neither is the bug.

The two fixes are not equally strong, and the difference is the tie-order assumption from The three assumptions to state out loud.

Making the ordering key unique gives a fully determined answer. Every row has exactly one predecessor, and the whole column is reproducible run after run.

Writing ROWS over a key that still has ties does not. It gives you the frame you meant, but which of two tied rows the sort emits first is unspecified. On 2025-03-03 the first of those two rows may report 650 or 750 depending on the plan, even though the second reports 1050 either way. The values are stable at every peer-group boundary and arbitrary inside one.

If the intermediate values are the point — and for a chart they are — make the key unique as well as writing ROWS.

GROUPS mode: counting distinct key values

GROUPS counts peer groups, which is usually what people mean by “the previous N distinct days”. Compare the same running sum in this mode:

SUM(amount) OVER (ORDER BY sale_date GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW)

row 1: groups {03-01}          -> 300
row 2: groups {03-01}          -> 300
row 3: groups {03-01, 03-02}   -> 350
row 4: groups {03-02, 03-03}   ->  50 + 300 + 400 = 750
row 5: groups {03-02, 03-03}   -> 750

What came back. 1 PRECEDING here means “one peer group back”, so row 4 sees its own date group (300 and 400) plus the single previous date group (50) and reports 750.

Compare the three modes on that same row 4. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW would have summed exactly two rows — 50 and 300 — giving 350. RANGE BETWEEN 1 PRECEDING AND CURRENT ROW would not even compile in PostgreSQL: when the ordering key is a date, the offset has to be an INTERVAL, not the bare number 1. GROUPS is the mode that counts calendar days actually present in the data.

Frame bound vocabulary

These are the endpoints, all combinable as BETWEEN <lower> AND <upper>. The mode keyword in front decides what “n” counts.

Frame exclusion

Each bound pair may be followed by a frame exclusion, which removes rows from a frame that has already been computed. Four forms:

Exclusion is available in PostgreSQL 11+ and SQLite 3.28+; MySQL 8 and SQL Server do not have it.

The exclusion is part of the frame clause, so it needs an explicit mode keyword in front of it. OVER (PARTITION BY g EXCLUDE CURRENT ROW) is a syntax error. This is the legal form:

AVG(x) OVER (PARTITION BY g
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
             EXCLUDE CURRENT ROW)

That is the leave-one-out group average — “how does this row compare to its peers, not counting itself” — with no correlated subquery.

Two more defaults worth memorizing

With no ORDER BY inside OVER, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Every row is a peer of every other, so the aggregate is constant over the whole partition. That is why the team averages in The schema and the anatomy of over came out flat down each team.

A frame clause attached to a ranking function is accepted but has no effect (The ranking family row_number rank dense_rank). The frame belongs to the window definition, not to the function, so a named window shared between RANK() OVER w and SUM(x) OVER w has to be legal for both — and the ranking function simply ignores the part it does not use.

Moving averages: “3-day” has three different meanings

A moving average — the average over a sliding stretch of recent rows — is where the ROWS/RANGE distinction stops being about ties and starts being about gaps.

Here is a daily table with two missing dates. That is enough to make one English phrase produce three different numbers.

d           amount
2025-04-01     10
2025-04-02     20
2025-04-03     30
2025-04-06     60      <- 04-04 and 04-05 have no rows
2025-04-07     70

The query below asks for a “3-day average” twice. avg_3_rows counts rows; avg_3_days counts calendar days, using an interval offset in RANGE mode. Those are the two lines to compare.

SELECT d, amount,
  ROUND(AVG(amount) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2)
    AS avg_3_rows,
  ROUND(AVG(amount) OVER (ORDER BY d RANGE BETWEEN INTERVAL '2 days' PRECEDING
                          AND CURRENT ROW), 2)
    AS avg_3_days
FROM daily ORDER BY d;

d           amount | avg_3_rows        | avg_3_days
2025-04-01     10  | 10.00  {10}       | 10.00  window [03-30, 04-01] -> {10}
2025-04-02     20  | 15.00  {10,20}    | 15.00  window [03-31, 04-02] -> {10,20}
2025-04-03     30  | 20.00  {10,20,30} | 20.00  window [04-01, 04-03] -> {10,20,30}
2025-04-06     60  | 36.67  {20,30,60} | 60.00  window [04-04, 04-06] -> {60}
2025-04-07     70  | 53.33  {30,60,70} | 65.00  window [04-05, 04-07] -> {60,70}

What came back. The two columns agree for the first three rows, because the series is unbroken there. They split the moment the gap is crossed.

Take 2025-04-06. The ROWS version averages the three most recent rows — 20, 30 and 60, from April 2, 3 and 6 — and gets 36.67. The RANGE version asks for rows whose date lies in the calendar interval [04-04, 04-06], finds only the 60, and reports 60.00.

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a 3-row window, and 3 rows equal 3 days only when the series is dense. On April 7 it reaches back to April 3 — a five-day span reported as a three-day average.

RANGE with an interval offset uses the calendar and is immune to gaps. PostgreSQL 11+ and MySQL 8 both support it (RANGE BETWEEN INTERVAL '2 days' PRECEDING ... in PostgreSQL, RANGE BETWEEN INTERVAL 2 DAY PRECEDING ... in MySQL). SQL Server has no value offsets in RANGE at all, and SQLite has no INTERVAL type, so on those engines you densify instead.

And there is a third answer, which is often the one the business actually wants:

3-day mean including missing days as ZERO:  04-06 -> (0 + 0 + 60)/3 = 20.00

Three defensible numbers — 36.67, 60.00, 20.00 — from one English phrase.

The choice behind them is whether an absent row means “no data” (skip it) or “zero activity” (count it). If it means zero, join to a calendar spine first — the generated one-row-per-day table from Lag and lead period over period, described in Dates the half open interval rule — and then ROWS is correct again, because the series is dense by construction.

Say which one you picked. Do not let the frame clause pick for you.

7. FIRST_VALUE, LAST_VALUE, NTH_VALUE — and the LAST_VALUE trap

Three functions pull a value out of a specific position in the frame — and an asymmetry in the default frame makes one of them work while its mirror image fails, silently.

Unlike the ranking family, these three are frame-sensitive. FIRST_VALUE returns the value from the first row of the frame, LAST_VALUE from the last, and NTH_VALUE(x, n) from the nth.

Now recall the default frame: UNBOUNDED PRECEDING AND CURRENT ROW. It starts at the first row of the partition and ends at the current row. Hold that in mind while reading the four output columns below.

SELECT month, revenue,
  FIRST_VALUE(revenue) OVER (ORDER BY month)                AS first_ok,
  LAST_VALUE(revenue)  OVER (ORDER BY month)                AS last_BROKEN,
  LAST_VALUE(revenue)  OVER (ORDER BY month
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_ok,
  NTH_VALUE(revenue, 2) OVER (ORDER BY month
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second
FROM monthly_revenue WHERE region = 'US' ORDER BY month;

month       revenue | first_ok | last_BROKEN | last_ok | second
2025-01-01    1000  |   1000   |    1000     |  1400   |  1200
2025-02-01    1200  |   1000   |    1200     |  1400   |  1200
2025-03-01    1100  |   1000   |    1100     |  1400   |  1200
2025-04-01    1500  |   1000   |    1500     |  1400   |  1200
2025-05-01    1650  |   1000   |    1650     |  1400   |  1200
2025-06-01    1400  |   1000   |    1400     |  1400   |  1200

What came back — and look at column 4. last_BROKEN is a verbatim copy of revenue.

The reason is mechanical. The default frame ends at CURRENT ROW, so the last row of the frame is always the current row, so LAST_VALUE returns the current row’s own value every time.

Nothing errors. You get a column that is right on the final row and wrong on every other one. If your test data has one row, or you eyeball the bottom of the result, it looks fine.

last_ok extends the frame to UNBOUNDED FOLLOWING, meaning “through the last row of the partition”, and gets the intended 1400 on every row.

FIRST_VALUE is correct only by accident. The default frame happens to start at UNBOUNDED PRECEDING, which is exactly what FIRST_VALUE needs. That asymmetry is the entire gotcha: the same default that makes one function work makes its mirror image fail.

Two fixes:

LAST_VALUE(revenue) OVER (ORDER BY month
                          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FIRST_VALUE(revenue) OVER (ORDER BY month DESC)     -- flip the sort instead

The second — flipping the sort and asking for the first value instead — is often clearer, and it lets the query planner reuse an index that is already stored in descending order.

NTH_VALUE needs the explicit frame too. With the default frame, NTH_VALUE(x, 2) returns NULL on the first row of every partition, because a one-row frame has no second element.

What these three assume. All three pick a particular row, so all three inherit the tie-order assumption from The three assumptions to state out loud. If the ORDER BY has duplicates, “the first row of the frame” is whichever tied row the sort emitted first, and that is not guaranteed to be the same one twice.

ORDER BY month is safe here, because monthly_revenue holds one row per month within a region. It would not be safe over raw event timestamps.

8. Top-N per group, done properly

The most-asked window question — “the top N rows within each group” — turns out to have three different, all legitimate, interpretations, one per ranking function.

“Top 2 players per team.” Three ranking functions, three different answers, and all three are the right answer to some question.

The CTE below computes all three numberings at once so they can be compared. The line that decides everything is which of rn, rk, or drk the outer WHERE filters on.

WITH r AS (
  SELECT team, player, points,
         ROW_NUMBER() OVER (PARTITION BY team ORDER BY points DESC, player) AS rn,
         RANK() OVER w AS rk, DENSE_RANK() OVER w AS drk
  FROM scores WINDOW w AS (PARTITION BY team ORDER BY points DESC)
)
SELECT * FROM r WHERE rn <= 2;    -- or rk <= 2, or drk <= 2

The three totals differ because Blue’s two 85s and Red’s two 80s are counted differently:

• rn <= 2 cuts Hal off mid-tie — he is row 3 on Blue even though he scored what Gus scored.
• rk <= 2 keeps him, because he is tied for second.
• drk <= 2 treats “80” as the second-best value in Red and admits both players who hold it, which is why Red contributes four rows.

ROW_NUMBER guarantees the row count. RANK guarantees fairness to ties. DENSE_RANK guarantees a count of distinct values. “Top 2” is ambiguous until you say which of those you meant.

That ambiguity has a downstream cost. A join that expected 2 rows per team and got 3 or 4 will fan out — the join multiplies rows instead of matching them one to one — so every sum computed after it is inflated (Joins five types and the row count formula that predicts fan out).

Why the tiebreaker is on ROW_NUMBER only

Look again at the query: , player appears in ROW_NUMBER’s own ORDER BY, but not in the shared window w. That is deliberate, and both halves matter.

ROW_NUMBER needs it. Without a tiebreaker, rn <= 2 on Blue returns Fay plus either Gus or Hal, and which one can change between runs (The ranking family row_number rank dense_rank).

RANK and DENSE_RANK must not have it. Adding , player to w would make the ordering total, and a total order has no ties, so RANK and DENSE_RANK could never tie either. All three columns would collapse to the same 4 rows and the whole distinction this section is about would disappear. A tiebreaker is exactly what RANK and DENSE_RANK exist to not have.

When not to use a window here

ROW_NUMBER requires sorting the entire table. That is the wrong shape when groups are few and rows per group are many.

The alternative is LATERAL — a join form in which the right-hand subquery may reference each left-hand row and is re-run once per row, so it can be a cheap indexed lookup instead of a sort. It costs groups × index-lookup (Top n per group three ways the fourth is chapter 02).

Put numbers on it. With 1,000 customers, 50M orders, and an index on (customer_id, order_ts DESC), LATERAL reads about 2,000 index entries while ROW_NUMBER sorts 50 million rows. Invert the ratio — 50M customers with 2 orders each — and the window function wins outright, because there is no small set of groups to loop over.

9. Gap and islands: sessionization

One window pattern is more than a single function call: turning a stream of timestamped rows into groups separated by gaps. It is worth learning as a shape, because the same three steps solve half a dozen unrelated-looking problems.

The interview favorite is “group these events into sessions, where a gap of more than 30 minutes starts a new session.” Sessionization is that task: chopping one user’s continuous stream of activity into visits.

You are asked to find islands — maximal runs of rows with no gap between them — separated by gaps. The technique generalizes to consecutive login days, uptime intervals, contiguous ID ranges, and “how long was this feature enabled”.

flowchart TD
    A["1 · LAG the ordered key<br/>within each partition"] --> B
    B["2 · flag boundaries<br/>is_new = 1 when the gap exceeds<br/>the threshold, or there is no previous row"] --> C
    C["3 · running SUM of the flag<br/>= how many islands started at or before me<br/>= a GROUP ID, constant within an island"] --> D
    D["4 · GROUP BY that id<br/>-> one row per session"]

    style B fill:#1d3557,color:#fff
    style C fill:#2d6a4f,color:#fff

Four steps, and the diagram is the algorithm.

Step 1 applies LAG to the ordered key within each partition, so every row can see its own predecessor’s timestamp.

Step 2 flags boundaries. A column is_new is set to 1 when the gap exceeds the threshold, or when there is no previous row at all, and 0 otherwise.

Step 3 takes a running sum of that flag. The result counts how many islands have started at or before this row, which makes it a group id.

Step 4 does an ordinary GROUP BY on that id, which collapses each island into one session row.

Why step 3 works. The flag is 1 exactly at the first row of each island and 0 everywhere inside one. A running sum of it therefore increments by exactly 1 when crossing into a new island and does not change anywhere else — so it is constant within an island and one larger in the next.

A quantity that is constant within a group and strictly increases between groups is a group key. That is the entire trick, and it is worth being able to say in one sentence.

Here it is in SQL. The events table holds one row per user action: user_id, event_ts, kind. Two CTEs implement steps 1-3, and the final SELECT is step 4.

WITH flagged AS (
    SELECT user_id, event_ts,
           CASE WHEN event_ts - LAG(event_ts) OVER w > INTERVAL '30 minutes'
                  OR LAG(event_ts) OVER w IS NULL
                THEN 1 ELSE 0 END AS is_new
    FROM events
    WINDOW w AS (PARTITION BY user_id ORDER BY event_ts)
), sessioned AS (
    SELECT user_id, event_ts,
           SUM(is_new) OVER (PARTITION BY user_id ORDER BY event_ts
                             ROWS UNBOUNDED PRECEDING) AS session_seq
    FROM flagged
)
SELECT user_id, session_seq,
       MIN(event_ts) AS started, MAX(event_ts) AS ended,
       MAX(event_ts) - MIN(event_ts) AS duration,
       COUNT(*) AS n_events
FROM sessioned
GROUP BY user_id, session_seq
ORDER BY user_id, session_seq;

Traced on one user with six events. The two intermediate columns (prev_ts and gap) are not in the query — they are shown so you can check is_new by hand:

event_ts    prev_ts    gap      is_new  running sum = session_seq
09:00:00    NULL       --         1            1
09:05:00    09:00:00    5 min     0            1
09:12:00    09:05:00    7 min     0            1
10:30:00    09:12:00   78 min     1            2
10:35:00    10:30:00    5 min     0            2
14:00:00    10:35:00  205 min     1            3

user  session  started   ended     duration  n_events
   7        1  09:00:00  09:12:00  00:12:00      3
   7        2  10:30:00  10:35:00  00:05:00      2
   7        3  14:00:00  14:00:00  00:00:00      1

What came back. The 78-minute and 205-minute gaps are the only two that exceed 30 minutes, so is_new fires exactly there. The running sum steps 1 → 2 → 3, and the final GROUP BY turns those three values into three session rows.

Three details decide whether this is correct in production.

ROWS UNBOUNDED PRECEDING is load-bearing

It is shorthand for ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Omit it and you get the default RANGE frame, which includes peers (Rows vs range vs groups and the default that causes the above). Two events with the identical timestamp then both get a running sum that includes both of their flags. A boundary event and the event after it land in the same session, or a session id skips a number.

Duplicate timestamps are the norm in event streams, so this is not a hypothetical.

The IS NULL arm of the CASE matters

For the first event of a user, LAG returns NULL. So NULL > INTERVAL '30 minutes' evaluates to UNKNOWN — SQL comparisons against NULL return a third truth value, not a boolean — and UNKNOWN is not true, so the CASE falls through to ELSE 0.

The result: every user’s first session gets id 0 while their later sessions start at 1. Harmless until you join sessions across users, or count sessions per user and wonder why the numbering is off by one.

This is the Three valued logic null is not a value it is an absence of knowledge three-valued-logic trap arriving inside a CASE instead of a WHERE.

A one-event session has duration zero, not “unknown”

Session 3 in the output above lasted 00:00:00. Whether to keep such sessions is a product decision. State it explicitly rather than letting the query decide by accident.

The ROW_NUMBER difference trick — islands in a consecutive sequence

There is a second, cheaper way to find islands, and it works whenever the key advances by a fixed step — calendar dates, or consecutive integers. It uses one window function instead of two, and the derivation is short enough to reproduce on a whiteboard.

Take the distinct login dates for one user from the logins table, number them with ROW_NUMBER, and subtract the number from the date. The third column is what to look at:

d           row_number   d - rn (as days)
2025-03-01      1          2025-02-28
2025-03-02      2          2025-02-28
2025-03-03      3          2025-02-28
2025-03-07      4          2025-03-03
2025-03-08      5          2025-03-03

Derivation. Inside a run of consecutive days, both d and rn increase by exactly 1 per row, so d - rn does not change — which is why the first three rows all show 2025-02-28.

At a gap of g days, d jumps by g while rn jumps by only 1, so d - rn jumps by g - 1, which is positive whenever g > 1. That is the jump from 2025-02-28 to 2025-03-03 at the fourth row.

The difference is therefore constant within a run and strictly larger after every gap — a valid group key, computed with one window function instead of two.

The cast in the query below (::int) is PostgreSQL’s syntax for turning the row number into an integer so it can be subtracted from a date.

WITH d AS (SELECT DISTINCT login_date FROM logins WHERE user_id = 7),
     g AS (SELECT login_date,
                  login_date - (ROW_NUMBER() OVER (ORDER BY login_date))::int AS grp
           FROM d)
SELECT MIN(login_date) AS streak_start, MAX(login_date) AS streak_end,
       COUNT(*) AS streak_len
FROM g GROUP BY grp ORDER BY 1;
-- 2025-03-01 .. 2025-03-03  len 3
-- 2025-03-07 .. 2025-03-08  len 2

DISTINCT is mandatory here, and that is the assumption this trick makes: one row per key value.

Two rows on the same date make rn advance while d stays put, so d - rn decreases — and a decrease is a new group key just as much as an increase is. The run splits, and the longest-streak number comes out too small, with no error anywhere.

The LAG-based form in the previous subsection handles duplicates natively and works for irregular steps. Prefer it unless the key is genuinely a dense sequence.

10. De-duplication with ROW_NUMBER

Almost every data-cleaning job runs into the same situation: a table with repeated rows for the same real-world entity, and a rule for which copy to keep.

De-duplication means collapsing those repeats down to one row per key, according to a rule you choose.

The general shape is three decisions: PARTITION BY the natural key, ORDER BY the rule that decides which duplicate wins, keep rn = 1. The natural key is the combination of columns that identifies the real-world thing — here, a customer plus an order timestamp.

The example runs against orders_raw, an ingestion landing table that has the same columns as orders plus an updated_at timestamp, and that may hold several rows for the same real order because the same event was delivered more than once.

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_id, order_ts
                              ORDER BY updated_at DESC, order_id DESC) AS rn
    FROM orders_raw
)
SELECT * FROM ranked WHERE rn = 1;

Read the OVER clause as the rule in English. Restart the numbering for each (customer_id, order_ts) pair. Within a pair, put the most recently updated row first, breaking any remaining tie by the highest order_id. Row number 1 is then the winner by definition.

What came back. One row per (customer_id, order_ts) pair — the surviving copy — with every original column plus rn, which is 1 on all of them.

Compared with the alternatives from Deduplication:

The ORDER BY inside OVER must be a total order, or the survivor is chosen arbitrarily and can change between runs. That means a de-duplication job that produces different output from the same input on a re-run.

updated_at DESC alone is not enough, because two copies can share an update timestamp. updated_at DESC, order_id DESC is enough, because order_id is unique.

Inspecting duplicates before deleting anything

Before you collapse anything, look at what you are about to throw away. This query keeps every copy and attaches the group size to each one:

SELECT * FROM (
    SELECT *, COUNT(*) OVER (PARTITION BY customer_id, order_ts) AS dup_n
    FROM orders_raw
) t WHERE dup_n > 1 ORDER BY customer_id, order_ts;

What came back. Every row belonging to a duplicated key, with a dup_n column holding how many copies that key has. Sorted by the key, the copies sit next to each other so you can read the differences off the screen.

GROUP BY ... HAVING COUNT(*) > 1 would tell you which keys are duplicated. This tells you how the rows differ, which is what you need in order to write the tie-break rule.

11. Cohort retention

Cohort retention is a full analytical query rather than a single function call. It earns a place here because it exercises two different uses of windows at once, and because its failure modes are about meaning rather than syntax.

A cohort is a set of users grouped by when they started — everyone who signed up in January is the January cohort. Retention is the share of a cohort still active n months later.

The query needs two things from windows: assign each user to a cohort, and compute a denominator (the cohort’s size) that is constant across every row of that cohort.

One extra table, holding one row per user per month in which that user did anything:

CREATE TABLE activity (user_id int, active_month date);   -- first of month

Here is the input. Five customers with signup dates, and their activity months:

customers                    activity (user_id, active_month)
1  signup 2025-01            1: Jan, Feb, Apr
2  signup 2025-02            2: Feb, Mar
3  signup 2025-02            3: Feb
4  signup 2025-03            4: Mar, Apr
5  signup 2025-03            5: Mar

Three CTEs, then one GROUP BY. The one line that uses a window is COUNT(*) OVER (PARTITION BY cohort_month) in cohort_sized — it computes each cohort’s size without collapsing the per-user rows, so the size can ride along into the join below.

WITH user_cohort AS (
    SELECT customer_id, DATE_TRUNC('month', signup_date)::date AS cohort_month
    FROM customers
), cohort_sized AS (          -- the denominator, from a window over one row per user
    SELECT customer_id, cohort_month,
           COUNT(*) OVER (PARTITION BY cohort_month) AS cohort_size
    FROM user_cohort
), user_months AS (           -- one row per (user, active month)
    SELECT DISTINCT a.user_id, cs.cohort_month, cs.cohort_size, a.active_month
    FROM activity a
    JOIN cohort_sized cs ON cs.customer_id = a.user_id
)
SELECT cohort_month, cohort_size,
       (EXTRACT(YEAR  FROM AGE(active_month, cohort_month)) * 12
      + EXTRACT(MONTH FROM AGE(active_month, cohort_month)))::int AS month_n,
       COUNT(*)                                    AS retained,
       ROUND(100.0 * COUNT(*) / cohort_size, 1)    AS pct
FROM user_months
GROUP BY cohort_month, cohort_size, 3
ORDER BY cohort_month, month_n;

Three PostgreSQL date functions appear there. DATE_TRUNC('month', d) rounds a date down to the first of its month, which is what turns a signup date into a cohort label. AGE(a, b) returns the interval between two dates. EXTRACT pulls the year and month out of that interval. Together they make month_n: how many whole months after signup the activity happened, counting the signup month itself as 0.

What came back, pivoted here into a grid so the shape is visible (the query returns one row per cohort per month_n):

cohort   size |  m0        m1        m2       m3
2025-01    1  | 1 (100%)  1 (100%)  --       1 (100%)
2025-02    2  | 2 (100%)  1 ( 50%)  --       --
2025-03    2  | 2 (100%)  1 ( 50%)  --       --

Check one cell by hand. The 2025-02 cohort is users 2 and 3, so size is 2. In March (m1) only user 2 was active, so retained is 1 and pct is 50.

Four things this query exposes.

COUNT(DISTINCT x) OVER (...) does not exist in PostgreSQL

It raises ERROR: DISTINCT is not implemented for window functions.

That is why user_months deduplicates to one row per (user, month) first, so a plain COUNT(*) over it is then correct. It is also why cohort_size is a window over user_cohort, which already holds exactly one row per user.

Interviewers ask this. The answer is: pre-aggregate to the grain where DISTINCT becomes unnecessary. The grain of a table is what one row of it represents — here, “one row per user” versus “one row per user per month”. Getting the grain right before counting is what removes the need for DISTINCT.

The denominator must be the cohort size, computed once

Suppose you moved COUNT(*) OVER (PARTITION BY cohort_month) down into user_months instead. It would then count user-months, not users, and every retention percentage would be wrong — in a way that still looks like a percentage, because it is still a number between 0 and 100.

Missing cells are absent, not zero

Cohort 2025-01 has no row at m2, because that user was inactive in March. The query returns 3 rows for that cohort, not 4.

A chart that plots the returned rows contiguously implies the user churned in March and returned in April. That happens to be true here, but the shape is invented by the missing row, not measured — and next time it will not be true.

The fix is the calendar-spine idea again: left-join a generate_series — the PostgreSQL function that manufactures one row per step of a range — over the month offsets, so the zeros become explicit rows (Dates the half open interval rule).

The triangle is not a rectangle

The 2025-03 cohort has had one month in which to be retained. The 2025-01 cohort has had four. So the grid is a triangle, and the empty upper-right is not data.

Averaging down a column mixes cohorts of different ages. Averaging across a row mixes calendar periods. Read cohort tables along the diagonal or not at all, and never quote a single “retention rate” computed over the whole table.

12. Performance: count the sorts

What does a window function cost? The model is unusually simple — it is almost entirely about sorting — which means you can predict the cost by reading the query rather than by running it.

A window function needs its input in PARTITION BY, ORDER BY order, so each distinct window specification costs one sort — unless an index already provides that order, or a previous window left the rows sorted compatibly.

How to read the plans below

The evidence is the query plan: the step-by-step strategy the planner prints when you write EXPLAIN in front of a query.

Read a plan from the inside out. The most-indented line runs first and feeds its rows to the line above it. Three node types appear below:

• Seq Scan reads the table front to back.
• Sort orders rows, and prints the key it sorted on.
• WindowAgg evaluates window functions over already-sorted input.

Counting the Sort lines is the whole cost model.

One sort, five window functions

All four OVER w calls below share one named window, and the fifth function uses a shorter but compatible one.

SELECT team, player, points,
       ROW_NUMBER() OVER w, RANK() OVER w, DENSE_RANK() OVER w,
       SUM(points)  OVER w, AVG(points) OVER (PARTITION BY team)
FROM scores
WINDOW w AS (PARTITION BY team ORDER BY points DESC, player);

WindowAgg                                  <- AVG OVER (PARTITION BY team)
  ->  WindowAgg                            <- the four functions that share w
        ->  Sort  Sort Key: team, points DESC, player
              ->  Seq Scan on scores

One Sort line, five window functions.

Functions sharing an identical window specification collapse into a single WindowAgg node, so the four OVER w calls cost one pass.

The fifth function (PARTITION BY team, no ORDER BY) gets its own WindowAgg node but no second sort. Its sort key {team} is a prefix of {team, points DESC, player}, and rows already sorted by the longer key are still grouped by team. PostgreSQL deliberately evaluates window clauses longest-key-first to make that reuse possible.

Two incompatible specs, two sorts

Now change one window so neither key is a prefix of the other.

SELECT ...,
       RANK()      OVER (PARTITION BY team   ORDER BY points DESC),
       ROW_NUMBER() OVER (PARTITION BY player ORDER BY points)
FROM scores;

WindowAgg
  ->  Sort  Sort Key: player, points
        ->  WindowAgg
              ->  Sort  Sort Key: team, points DESC
                    ->  Seq Scan on scores

Two Sort lines, stacked. The rows have to be re-sorted between the two window nodes, because {player, points} and {team, points DESC} share no prefix.

Cost goes from one n log n — the standard cost of sorting n rows — to two. And each Sort that needs more memory than work_mem (the per-operation memory budget PostgreSQL allows before it starts writing temporary files) spills to disk as an external merge sort, typically 3-10x slower than an in-memory one.

So the practical rule: collapse window specifications onto as few distinct (PARTITION BY, ORDER BY) pairs as the logic allows, and declare them once in a WINDOW clause so it is visible at a glance whether two windows really are identical. A stray DESC or an extra tiebreaker in one of five windows silently doubles the sorts.

Four more levers

Two of those rows need a word.

Frame shape. A frame that only ever grows — UNBOUNDED PRECEDING .. CURRENT ROW — lets the engine keep one accumulator and add to it. That is constant work per row no matter how large the frame gets.

Invertibility is whether a running aggregate can undo a value. SUM can subtract the row leaving a sliding window, so a moving sum stays constant-time per row. There is no way to un-MIN a value, so when the current minimum itself slides out of frame the engine must re-scan the whole frame to find the next one.

The last row of the table is the practical counterpart to What a window function is and more importantly when it runs. LIMIT cannot reduce window work, because it runs at step 9. WHERE can, because it runs at step 2. If a window query is slow, the fix is almost always a predicate or an index, never a LIMIT.

Memory

Sorting is half the story; buffering is the other half.

PostgreSQL holds each partition in a tuplestore — an in-memory holding area for rows that overflows to temporary disk files past work_mem.

That makes the missing-PARTITION BY assumption from The three assumptions to state out loud a performance problem as well as a correctness one. One partition covering the whole table (OVER () with no PARTITION BY) over 100M rows will spill. A PARTITION BY user_id over the same data holds only one user at a time, provided the input arrives sorted.

13. Cheat sheet

Everything above, compressed for rereading before an interview: what each function returns, what it assumes, what each omitted clause defaults to, and the one-line recipe for every pattern in the chapter.

Every function, and its trap

Every default

The three questions to ask of any window query

Every pattern, in one line each

SUM(SUM(x)) OVER () is not a typo

One row of that table looks like a mistake and is not. Percent-of-total after a GROUP BY is written with a double aggregate:

SELECT c.region,
       SUM(o.amount_cents)                                                AS rev,
       SUM(SUM(o.amount_cents)) OVER ()                                   AS total,
       ROUND(100.0 * SUM(o.amount_cents) / SUM(SUM(o.amount_cents)) OVER (), 1) AS pct
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.region;

region |  rev   | total  | pct
US     | 100000 | 127000 | 78.7
EU     |  27000 | 127000 | 21.3

What came back. Two rows, one per region, each carrying the grand total and its own share of it.

Why the inner SUM is required. The window runs at step 5, after GROUP BY at step 3. So by the time the window sees anything, the rows have already been grouped — its input is two region rows, not the raw orders. A window function’s argument has to be something that is legal against a grouped row, and that means an aggregate.

Read it right to left: SUM(SUM(x)) OVER () is “sum, across the group rows, of each group’s sum.”

The empty OVER () is the deliberate whole-table partition from the assumptions table. Here it is exactly what you want, because the grand total 127000 is the denominator every region should divide by.

Once you can say why the inner SUM is required, the double aggregate stops looking like a typo and becomes the shortest correct way to write percent-of-total in one pass.

The one-line version of the chapter: a window function computes over a frame without collapsing rows, and it runs after WHERE/GROUP BY/HAVING and before DISTINCT/ORDER BY/LIMIT — so you filter its output in an outer query, LIMIT never makes it cheaper, and the two things that silently give wrong answers are the default RANGE frame (which includes every peer of the current row) and a ROW_NUMBER over an ordering that is not total.

Next: 03 — Database Internals — indexes, query plans, transactions, and isolation: why the planner chose that sort, and what it costs.