Beyond the basics

Lookup sheet for stage 3. The question it exists to answer: which window function, frame or tool answers this question, and does it obey the frame?

All counts below are against the small fixture: eight customers, twelve orders, eight countries, described in The Dataset. The JSON table adds the four events rows from the same page.

The window function vocabulary

Every function this stage taught, and the column a reader comes back for: whether it reads the frame or ignores it.

Verified on the fixture: lag(amount, 2, '0') gives customer 1's three orders 0, 0, 120.00. last_value(amount) OVER (PARTITION BY customer_id ORDER BY amount) gives each row its own amount, not the partition's largest, because the default frame ends at the current row; widening the frame to the whole partition makes it 120.00 for customer 1 and 999.99 for customer 4. Ordered by amount, percent_rank and cume_dist give the first four rows 0.000, 0.000, 0.182, 0.273 and 0.167, 0.167, 0.250, 0.333.

The three frame modes

A frame is the subset of the partition a frame-sensitive function actually reads for the current row.

The bars are what each row actually reads. Under the default both are the full peer group, which is why a running total with no explicit frame is not per row, and why the surprise shows up only once the ordering column ties.

Two restrictions a reader will hit, both re-run directly. First, a RANGE offset bound needs exactly one ORDER BY column: sum(amount) OVER (ORDER BY amount, id RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) fails with ERROR: RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column, SQLSTATE 42P20, since the offset does arithmetic on the ordering value, which only means something with one subtractable column. GROUPS has no such limit, verified: the identical query with GROUPS in place of RANGE runs against two ORDER BY columns, since counting peer groups only needs equality on the whole tuple, not subtraction. Second, the tied-row behaviour: with the fixture's two 10.00 orders, the plain default gives both 20.00, since RANGE frames every peer together; naming ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW instead splits them into 10.00 and 20.00, in whichever order the tie lands. A running total with no explicit frame is a running total over peers, not over rows, and the two agree only when the ordering column never ties.

Where a window function may run, and where it may not

Tied to Evaluation order: a window is computed after FROM, WHERE, GROUP BY and HAVING have all finished, in the same phase that builds the SELECT list.

All four share one SQLSTATE for one reason: at the point each clause runs, the window has not been computed yet, so there is nothing for it to filter, group or nest on. Filtering on one means computing it a layer down, in a derived table or a CTE, and testing the alias from the query wrapped around it.

LATERAL

LATERAL lets a subquery in FROM see a column from a table already named earlier in the same FROM clause, which an ordinary derived table cannot do. ON true is the idiom that goes with it: the correlation already happened inside the lateral subquery's own WHERE, so the join itself has nothing left to check and ON true just accepts whatever came back, once per outer row.

Order in FROM carries meaning here: a lateral item sees only what is already to its left, so writing it before the table it needs fails with ERROR: missing FROM-clause entry for table "c", 42P01, no HINT, since c has not been reached yet. Omitting LATERAL entirely fails differently: ERROR: invalid reference to FROM-clause entry for table "c", DETAIL: There is an entry for table "c", but it cannot be referenced from this part of the query., HINT: To reference that table, you must mark this subquery with LATERAL., 42P01.

Recursive WITH

A recursive WITH has three parts, in a fixed order: an anchor term that runs once and fixes the starting rows, UNION or UNION ALL, and a recursive term, the only one of the three allowed to mention the CTE's own name.

Termination is the query's job, not the engine's: nothing checks that a recursive term will eventually stop, and one with no failing condition produces rows forever. Two things actually stop it. A condition on a column that changes every round, typically a depth counter, guarantees a bounded number of rounds: WHERE depth < 5 at most five. Deduplication is the other: UNION discards a row that repeats one already produced, and if the recursive term can only ever produce values from a finite set, the round eventually has nothing new to add, verified with WITH RECURSIVE dedup(n) AS (SELECT 1 UNION SELECT (n + 1) % 3 FROM dedup) SELECT n FROM dedup, which returns 1, 2, 0 and stops. LIMIT on the outer query looks like a third way, and it does return, since PostgreSQL evaluates rounds lazily, but it only stops asking for more; sorting the result or joining it elsewhere still tends to pull every round first, so it inspects a query under construction rather than substituting for a real stopping condition. CYCLE id SET is_cycle USING path catches a cycle a stopping condition would miss: the moment a row's id repeats one already seen, that row is returned once, marked is_cycle true, and the recursion stops there rather than looping. PostgreSQL has had CYCLE since release 14; earlier, or on an engine without it, the same guard is hand-written as an array column and a WHERE NOT id = ANY(seen) test.

JSON

The one-line rule: -> returns jsonb so it chains, each step handing another document to the next ->; ->> returns text, which has nothing left to descend into, so it only ever ends a chain. Verified: pg_typeof(payload -> 'channel') is jsonb, pg_typeof(payload ->> 'channel') is text; payload -> 'items' -> 0 ->> 'sku' gives A1 for events 1 and 2 and NULL for events 3 and 4, since a missing key or an out-of-range index returns NULL rather than an error. @> matches events 1 and 4 on {"channel": "web"}; ? matches event 2 alone on coupon.

Which tool answers which question

The stage's centrepiece: three questions, each with two or three correct spellings, verified against the fixture.

Verified: all three spellings of "newest order per customer" return the identical seven ids, 102, 104, 105, 107, 109, 111, 112, once the ordering breaks ties correctly with shipped_at DESC NULLS LAST, id DESC. Dropping NULLS LAST lets an unshipped order win the "newest" seat, since PostgreSQL's default for DESC is NULLS FIRST. Reading the same ranked query backwards, rn > 1 instead of rn = 1, gives the five rows to delete rather than the seven to keep, and the two sets never overlap.

The diagnostics of the stage

Every error a lesson quoted, re-run and confirmed, plus the two frame errors this sheet needed and verified directly.

PostgreSQL and SQLite

What travels unchanged, verified directly: every ranking and aggregate window function, the ROWS and RANGE frame modes and the tie behaviour that splits them, lag, lead, first_value, last_value, nth_value, ntile, percent_rank, cume_dist, the WINDOW clause, ->, ->> and json_extract, and WITH RECURSIVE with the same anchor-union-recursive shape.