SQL Analytical Functions: Partitions, Frames, and Calendar Gaps

Window calculations need the right entity and order

LAG without a product partition can compare one product with another. LAG with the partition still means the previous observed row, which may not be the previous calendar month. A running total also needs a frame and a deterministic order. This PostgreSQL 16 guide makes those differences visible in small fixtures before adding ranking and distribution functions.

Keep row-level output while calculating across related rows

An ordinary GROUP BY aggregate reduces each group to one result row. A window expression adds a calculation to each row of its input. PARTITION BY chooses independent groups for that calculation; the window ORDER BY chooses their sequence. The final query ORDER BY is a separate display instruction. None of these choices should be inferred from physical storage order.

Compare a global LAG with a correctly partitioned LAG

WITH readings(id,product,observed_on,amount) AS (
 VALUES (1,'A',DATE '2026-01-01',10),(2,'B',DATE '2026-01-01',100),
        (3,'A',DATE '2026-01-01',20),(4,'A',DATE '2026-03-01',30),
        (5,'B',DATE '2026-02-01',110)
)
SELECT product,id,observed_on,amount,
 LAG(amount) OVER(ORDER BY observed_on,id) AS misleading_global_previous,
 LAG(amount) OVER(PARTITION BY product ORDER BY observed_on,id) AS product_previous,
 SUM(amount) OVER(PARTITION BY product ORDER BY observed_on,id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
 LAST_VALUE(amount) OVER(PARTITION BY product ORDER BY observed_on,id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS final_observed
FROM readings ORDER BY product,observed_on,id;

product | id | observed_on | amount | misleading_global_previous | product_previous | running_total | final_observed
A | 1 | 2026-01-01 | 10 | NULL | NULL | 10 | 30
A | 3 | 2026-01-01 | 20 | 100 | 10 | 30 | 30
A | 4 | 2026-03-01 | 30 | 110 | 20 | 60 | 30
B | 2 | 2026-01-01 | 100 | 10 | NULL | 100 | 110
B | 5 | 2026-02-01 | 110 | 20 | 100 | 210 | 110

The misleading column is syntactically valid but crosses product boundaries. Product A’s second reading is compared with B’s 100; its March reading is compared with B’s 110. The product partition fixes that entity error. The unique ID breaks same-date ties for this fixture. If IDs are not an authoritative sequence, define another business order before calling a row “previous.”

Use frames for the actual question

The running total explicitly includes rows from the partition start through the current row. PostgreSQL’s default ordered frame includes same-order peers; ordering only by date would give both January A rows the same end-of-date total. LAST_VALUE needs the full-partition frame shown here to return the final observed amount for every row. Under an ordinary current-row frame, it is not a shortcut for “latest value in the table.”

LEAD reads an already present following row. It does not forecast a future value that has not been observed. A full-partition last value can also leak future information into a historical feature calculation. For prediction-time features, filter availability and event time to the cutoff before computing the intended historical window.

Build a calendar before comparing calendar periods

For the next fixture, absence means zero sales in a complete feed. First aggregate multiple product-month facts, then join every product to every reporting month. This makes LAG refer to the previous calendar month:

WITH sales(product,month,amount) AS (
 VALUES ('A',DATE '2026-01-01',10),('A',DATE '2026-01-01',20),
 ('A',DATE '2026-03-01',30),('B',DATE '2026-01-01',100),
 ('B',DATE '2026-02-01',110)
), months AS (
 SELECT m::date AS month FROM generate_series(
 TIMESTAMP '2026-01-01',TIMESTAMP '2026-03-01',INTERVAL '1 month') m
), totals AS (
 SELECT product,month,SUM(amount) AS amount FROM sales GROUP BY product,month
), dense AS (
 SELECT p.product,m.month,COALESCE(t.amount,0) AS amount
 FROM (VALUES ('A'),('B')) p(product) CROSS JOIN months m
 LEFT JOIN totals t USING(product,month)
)
SELECT product,month,amount,
 LAG(amount) OVER(PARTITION BY product ORDER BY month) AS prior_calendar_month
FROM dense ORDER BY product,month;

product | month | amount | prior_calendar_month
A | 2026-01-01 | 30 | NULL
A | 2026-02-01 | 0 | 30
A | 2026-03-01 | 30 | 0
B | 2026-01-01 | 100 | NULL
B | 2026-02-01 | 110 | 100
B | 2026-03-01 | 0 | 110

For A, the prior observed transaction in March was January’s 20, while the prior calendar month’s total is February’s 0. Those are different measures. If February data is missing because the feed failed, retain a missing/completeness marker instead of zero. A finite ROWS frame likewise counts rows, not calendar days.

Keep rank ties distinct from row order

WITH scores(id,score) AS (VALUES (1,10),(2,20),(3,20),(4,30))
SELECT id,score,ROW_NUMBER() OVER(ORDER BY score,id) AS row_number,
 RANK() OVER(ORDER BY score) AS rank,
 DENSE_RANK() OVER(ORDER BY score) AS dense_rank,
 ROUND(PERCENT_RANK() OVER(ORDER BY score)::numeric,3) AS percent_rank,
 ROUND(CUME_DIST() OVER(ORDER BY score)::numeric,3) AS cumulative_fraction
FROM scores ORDER BY score,id;

id | score | row_number | rank | dense_rank | percent_rank | cumulative_fraction
1 | 10 | 1 | 1 | 1 | 0.000 | 0.250
2 | 20 | 2 | 2 | 2 | 0.333 | 0.750
3 | 20 | 3 | 2 | 2 | 0.333 | 0.750
4 | 30 | 4 | 4 | 3 | 1.000 | 1.000

ROW_NUMBER selects a unique sequence using ID, while the ranking functions intentionally compare score alone. PERCENT_RANK uses (rank − 1) / (partition rows − 1), with zero for a one-row partition. CUME_DIST includes all peers at the current ordering value. These are fractions from zero to one under this ordering, not interchangeable percentile definitions.

Do not optimize away required history

Filtering before the window changes its input. For a running balance displayed only in March, compute the required history first and filter the displayed result afterward, or supply a verified opening balance. Pre-aggregation is useful only when its grain matches the window question. Inspect sorts, partition sizes, and actual plans; an index is a possibility, not a guarantee of faster execution.

Validate intermediate rows

Test two entities, equal dates, a missing calendar period, and tied scores. Verify the first row of each partition has the expected missing predecessor. Check that the final running totals are 60 for A and 210 for B. These invariants expose mistakes that a plausible-looking chart can hide.

FAQ

Does PARTITION BY sort the final result? No. Is a previous row necessarily the previous month? Only after establishing one row per month with the required complete time axis. Does LEAD predict next month? No; it reads another existing row.

Primary references: PostgreSQL window functions, series generation, and the running-total fixture.