Running Totals, LAG/LEAD and Frames

Description

The previous lesson used windows to rank rows. This one uses them to look along the rows: a running balance per account, the change from last month, a 7-day moving average. These are everyday questions on any dashboard that tracks money, traffic or sales over time.

All three are short queries, and all three hide a bug that tends to survive testing:

• A running balance shows the same number on two payments made in the same second.
• "Change versus last month" compares March with January whenever February had no sales.
• A "7-day average" quietly becomes a 7-row average, which spans two weeks when some days are empty.

Each one comes down to a single idea, the window frame - which rows around the current one a window function actually looks at. Step by step:

1. Running totals, and the frame you get by default.
2. LAG and LEAD - peeking at the previous and next row.
3. Moving averages counted in rows versus in days.
4. FIRST_VALUE and LAST_VALUE, and why LAST_VALUE surprises everyone.
5. Each row's share of a total.

Examples use five payments (paid_on, amount). Two were paid on March 2, and there was nothing on March 3. Every result was produced by PostgreSQL 18.

Running totals and the frame

With an ORDER BY inside OVER, an aggregate becomes cumulative. But the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE includes the current row's peers - every row with the same ORDER BY value:

SELECT id, paid_on, amount,
       SUM(amount) OVER (ORDER BY paid_on) AS default_frame,
       SUM(amount) OVER (ORDER BY paid_on, id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM payments
ORDER BY paid_on, id;

Payments 2 and 3 share a date, so the default frame gives both the total after both of them. For a per-transaction running balance you want one row at a time: make the order unique (ORDER BY paid_on, id) and say ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The peer behavior is right when you want a total as of the end of each day.

Frames have three modes: ROWS counts physical rows, RANGE compares ORDER BY values (including an offset like INTERVAL '6 days'), and GROUPS counts peer groups. Without an ORDER BY in OVER, the frame is the whole partition - SUM(x) OVER () is the grand total on every row.

LAG and LEAD

LAG(x) returns x from the previous row of the window's order, LEAD(x) from the next. LAG(x, n, default) looks n rows back and returns default instead of NULL when there's no such row. Aggregate to one row per period first (here with a WITH query, the next module's subject), then compare:

WITH daily AS (
    SELECT paid_on, SUM(amount) AS revenue FROM payments GROUP BY paid_on
)
SELECT paid_on, revenue,
       LAG(revenue) OVER (ORDER BY paid_on)           AS previous,
       revenue - LAG(revenue) OVER (ORDER BY paid_on) AS change
FROM daily
ORDER BY paid_on;

Two things to notice. The first row has no previous one, so its change is NULL - usually what a report wants, since "change from nothing" is undefined. And March 4's "previous" is March 2: LAG steps back one row, not one day. When the calendar has gaps, either fill them first (generate_series, in the time-series module) or check that the previous row really is the previous period: CASE WHEN LAG(day) OVER w = day - 1 THEN LAG(revenue) OVER w END.

A percentage change divides by the previous value - guard it: 100.0 * (revenue - prev) / NULLIF(prev, 0) returns NULL instead of a division-by-zero error.

Moving averages: ROWS or RANGE

WITH daily AS (
    SELECT paid_on, SUM(amount) AS revenue FROM payments GROUP BY paid_on
)
SELECT paid_on, revenue,
       ROUND(AVG(revenue) OVER (ORDER BY paid_on
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg_3_rows,
       ROUND(AVG(revenue) OVER (ORDER BY paid_on
             RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW), 2) AS avg_3_days
FROM daily
ORDER BY paid_on;

On March 4, "the last 3 rows" reaches back to March 1 (there's no March 3 row), while RANGE ... INTERVAL '2 days' PRECEDING covers only March 2-4. A "7-day average" is RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW - or ROWS BETWEEN 6 PRECEDING AND CURRENT ROW over a series with one row for every day, empty days included. Note both give partial averages at the start, where fewer days exist; filter those rows out if a full window is required.

FIRST_VALUE, LAST_VALUE and the default frame

The default frame stops at the current row, which makes LAST_VALUE useless without a frame clause:

SELECT id, amount,
       LAST_VALUE(amount) OVER (ORDER BY id) AS last_default,
       LAST_VALUE(amount) OVER (ORDER BY id
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_whole
FROM payments
ORDER BY id;

With the default frame, the "last" row is always the current one. Extend the frame to UNBOUNDED FOLLOWING - or flip the order and use FIRST_VALUE(...) OVER (ORDER BY id DESC).

Shares of a total

SUM(x) OVER () puts the grand total on every row (or OVER (PARTITION BY region) the region's total), so each row can compute its share:

WITH daily AS (
    SELECT paid_on, SUM(amount) AS revenue FROM payments GROUP BY paid_on
)
SELECT paid_on, revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total
FROM daily
ORDER BY paid_on;

Windows can wrap aggregates directly, too: in a grouped query, SUM(SUM(amount)) OVER (ORDER BY paid_on) is the running total of the daily sums - the inner SUM is the GROUP BY aggregate, the outer one the window.

Frame cheat sheet

Where it breaks

• Running totals with ties in the ORDER BY - peers share a total. Add a unique tiebreaker and a ROWS frame.
• LAG across missing periods - compares with the last row that exists, not the previous day or month.
• A "7-day" average with ROWS 6 PRECEDING over data with gaps - it spans more than seven days.
• LAST_VALUE with the default frame - returns the current row.
• Percentage change with a zero or missing previous value - division by zero, or NULL; decide which and use NULLIF.
• PARTITION BY forgotten - one account's running balance continues from another's.

Tradeoffs

• Window functions vs a self-join for "previous row": LAG reads each row once; a self-join on prev.day = cur.day - 1 handles gaps naturally (a missing day simply doesn't match) but costs a join.
• RANGE with an interval vs filling gaps first: the interval frame is concise; a generated calendar (next modules) also produces rows for empty days, which a report usually needs anyway.
• Computing in SQL vs in the application: a running balance over millions of rows is one ordered scan in the database; pulling rows out to sum them in code moves all the data across the network.

Going further. The deep dive (next tab) covers how a moving frame is computed - why SUM over a sliding window is cheap and MAX isn't, what EXCLUDE and GROUPS do, why there's no COUNT(DISTINCT) window, when a frame spills to disk, and how ledgers keep running balances at scale.

Check yourself

1. Two deposits of $50 were posted at the same instant. Why does SUM(amount) OVER (PARTITION BY account ORDER BY posted_at) show the same balance on both, and how do you fix it?

2. February had no orders. What does LAG(revenue) OVER (ORDER BY month) return for March?

3. Why does LAST_VALUE(price) OVER (ORDER BY day) return each row's own price?

Key takeaways

• With ORDER BY in OVER, aggregates are cumulative; the default frame is RANGE ... CURRENT ROW, which includes peers.
• For a per-row running total, order by a unique key and use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• LAG/LEAD step by rows, not by calendar periods - mind the gaps; guard divisions with NULLIF.
• Moving averages: ROWS n PRECEDING counts rows, RANGE INTERVAL ... PRECEDING counts time.
• LAST_VALUE needs a frame that reaches the end; SUM(x) OVER () gives the total for shares.