All 22 modules are open from the start — nothing here is
locked, and nothing costs anything. Sign in so your progress, titles and
credentials stay with you, on every device you use.
Free forever, with your Google account. No password, no payment.
Frames: which rows around this one the calculation may see
Every window in the last module looked at a whole partition at once. Adding
a frame — a range of rows relative to the current one —
turns the same functions into running totals, moving averages and
month-on-month change. It also introduces a default that is wrong often
enough to be worth a lesson of its own.
Ready?
1
A Window Inside the Window
SUM(x) OVER () gave every row the same grand total. Add an
ORDER BY inside the window and it becomes a
running total: each row shows the sum of everything up
to and including itself.
SUM(revenue) OVER (ORDER BY mon ROWS UNBOUNDED PRECEDING)
That trailing clause is the frame — which rows of the
partition this row's calculation may see. Here it means "every
row from the start down to me". Change the frame and the same function
answers a different question:
∑
ROWS UNBOUNDED PRECEDING
Start of partition to here. A running total — the frame grows as you go down.
↔
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
Three rows wide, sliding. A moving average.
⇄
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
The whole partition, regardless of where this row sits.
A moving average is worth one caution. At the start of the series the
frame is simply shorter — the first row averages one
value, the second averages two, and only from the third is it a genuine
three-month mean. Postgres does not pad and does not warn, so the first
points on the chart are computed differently from the rest.
Aggregate first, then window
Monthly totals have to exist as rows before they can accumulate, so
these queries are nearly always a CTE: group into months, then window
over the result. Trying to do both in one SELECT is where
the "column must appear in the GROUP BY clause" errors come from.
Quick check
A 12-month series with a ROWS BETWEEN 2 PRECEDING AND CURRENT ROW average. How many values does the second month's average cover?
2
The Default Frame Is Not the One You Want
Here is the trap, and it is a good one because it is
invisible on most data.
The moment a window has an ORDER BY, it acquires a default
frame of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
The word is RANGE, and RANGE works on
values, not rows: it includes every peer —
every row with the same ORDER BY value — as though they
were all the current row.
a running total over tied amounts
amount
RANGE (the default)
ROWS UNBOUNDED PRECEDING
9.00
18.00
9.00
9.00
18.00
18.00
12.00
42.00
30.00
12.00
42.00
42.00
19.00
61.00
61.00
The two 9.00 orders are peers, so RANGE gives both of them the total
of both. The running total jumps by the whole tied group at once, and
the columns agree again as soon as the ties are behind them — which is
what makes this so easy to miss.
ROWS counts rows, which is what a running
total almost always means. On a monthly series there are no ties and the
two frames agree exactly — so people write RANGE for years
without noticing, until the day the ordering column has duplicates in
it.
The same cause, wearing a different hat
LAST_VALUE is the other place this surfaces, and it looks
completely unrelated until you know.
-- returns this row's own amount, not the partition's last
LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY ordered_at)
The default frame ends at the current row. So the "last
row of the frame" is the current row, and
LAST_VALUE hands back the value you already had — a column
that looks like a duplicate of another column, which is usually how the
bug gets spotted. Opening the frame fixes it:
LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY ordered_at
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)
FIRST_VALUE has no such problem, because the frame starts
where you expect. Many people sidestep LAST_VALUE entirely
by using FIRST_VALUE with the ordering reversed — no frame
clause, nothing to get wrong.
Quick check
A running total of daily sales, ordered by date, using the default frame. Two sales on the same day. What happens?
3
Reaching to the Row Before and After
LAG(x) returns x from the previous row of the
window; LEAD(x) from the next. Between them they answer
every "compared with last time" question there is.
LAG(revenue) OVER (ORDER BY mon) AS prev_revenue,
revenue - LAG(revenue) OVER (ORDER BY mon) AS change
Both take two more optional arguments —
LAG(x, n, default). The n is how many rows
back, so LAG(revenue, 12) is the same month last year. The
third replaces the NULL at the edge.
Think before defaulting that NULL to zero
LAG(revenue, 1, 0) makes the first month's change equal
its entire revenue, and every chart will read that as explosive growth
from nothing. For a month with no predecessor the honest answer is not
zero, it is unknown — which is what NULL already
says. Default it only when zero is genuinely the right value.
Pair LAG with PARTITION BY and you get
intervals per entity — how long between one customer's orders:
ordered_at - LAG(ordered_at) OVER (PARTITION BY user_id
ORDER BY ordered_at) AS gap_days
The partition restarts the reaching-back at each user, so nobody's first
order is compared against someone else's last. Subtracting two
DATE columns gives an integer number of days — real dates
doing real arithmetic, which is what the Postgres migration bought.
LAG gives the previous row, not the previous month
This dataset has no paid orders in November 2023, so that month has no
row — and LAG happily reaches past it to October. A
"month-on-month" figure that skips absent months is a real reporting
bug. The fix is module 3-06: generate the months with
generate_series and LEFT JOIN revenue onto
them, so the gaps exist as zeros and can be reached.
Quick check
You want each customer's days-since-previous-order, and you omit PARTITION BY user_id. What do you get?
4
Putting a Trend Report Together
A month-on-month trend is just these pieces stacked on one set of
monthly totals: the month, its revenue, the running total, the previous
month, the change, and that change as a percentage.
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY mon))
/ NULLIF(LAG(revenue) OVER (ORDER BY mon), 0), 1) AS pct_change
NULLIF(prev, 0) is the load-bearing part. A month with no
revenue would otherwise be a division by zero, and in Postgres that is
an error that fails the entire query — not a NULL, not
an infinity. One bad month and the report returns nothing at all.
When 100 vs 100.0 actually matters
Module 2-05 warned that integer division silently returns zero. It
bites only when both operands are integers, and
revenue here is NUMERIC — so
100 and 100.0 give the same answer. The
moment you divide one COUNT by another it is real again,
because COUNT returns a bigint:
Writing 100.0 everywhere costs nothing and means never
having to work out which case you are in.
One habit worth carrying out of this module: write the frame
out. ROWS UNBOUNDED PRECEDING is four extra words,
it is what you meant, and it is correct on data that has ties as well as
data that does not. Relying on the default only works until the data
changes underneath you.
Next: funnels and cohorts
Module 4-03 puts these to work on the two reports every product team
asks for — how many people got from one step to the next, and whether
the ones who signed up in March are still here in June.
Quick check
A percentage-change report runs fine for months, then one morning returns no rows and an error. What is the most likely cause?
0 of 9 completed
Loading the tables…
01
To do
SUM(x) OVER () gave every row the same grand total. Add an
ORDER BY inside the window and it becomes a
running total — each row showing the sum of everything
up to and including itself.
SUM(revenue) OVER (ORDER BY mon ROWS UNBOUNDED PRECEDING)
The ROWS UNBOUNDED PRECEDING is the frame:
which rows of the window this row's calculation may see. Here, "every
row from the start of the partition down to me". The next station is
about why writing it out matters.
The monthly totals have to exist before they can accumulate, so this is
a CTE — aggregate first, then window over the result.
Your task: from paid orders, return
each month as mon, that month's revenue, and
the running total to date. Order by mon.
Month is date_trunc('month', ordered_at)::date, from module
2-04.
query.sql
PostgreSQLCtrl↵ to run
Hint
SUM(revenue) OVER (ORDER BY mon ROWS UNBOUNDED PRECEDING) AS running.
Output
02
To do
This is the module's real content, and it is invisible on most data.
Adding ORDER BY to a window sets a default frame of
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The word
is RANGE, and RANGE works on values: it
includes every peer — every row with the same
ORDER BY value — as though they were all the current row.
So a running total over tied values jumps by the whole tied group at
once:
amount RANGE (the default) ROWS UNBOUNDED PRECEDING 9.00 18.00 9.00 9.00 18.00 18.00
ROWS counts rows, which is what a running
total almost always means. On a monthly series there are no ties and the
two agree exactly — which is how people write RANGE for
years without noticing, until the day the ordering column has
duplicates.
Your task: for paid orders ordered by
amount, return order_id, amount, and a
running total using an explicit ROWS frame.
Break ties by order_id. Order by amount, then order_id.
query.sql
PostgreSQLCtrl↵ to run
Hint
Add the frame: OVER (ORDER BY o.amount, o.order_id ROWS UNBOUNDED PRECEDING). Including order_id in the ORDER BY also removes the ties, so both effects point the same way.
Output
03
To do
LAG(x) returns x from the
previous row of the window. It is how any
"compared with last time" question is written.
LAG(revenue) OVER (ORDER BY mon)
The first row has nothing before it, so it gets NULL. That
is honest rather than annoying — there genuinely is no previous month —
and the next station deals with it.
One thing to be precise about: LAG returns the previous
row, not the previous calendar month. This series has
gaps — no paid orders in November 2023, or April 2024 — so the row
before March 2024 is February 2024, but the row before June 2024 is
March 2024. If the report must show empty months, generate the months
first with generate_series from module 3-06 and left join
the revenue onto them.
Your task: from paid orders, return
mon, revenue, and the previous row's revenue as
prev_revenue. Order by mon.
query.sql
PostgreSQLCtrl↵ to run
Hint
LAG(revenue) OVER (ORDER BY mon) AS prev_revenue.
Output
04
To do
Subtract and you have month-on-month change — the number every trend
report is actually made of.
revenue - LAG(revenue) OVER (ORDER BY mon) AS change
LAG takes two more optional arguments:
LAG(x, n, default). The n is how many rows
back — LAG(revenue, 12) is the same month last year. The
third is what to use instead of NULL at the edge.
Use that default carefully. Writing
LAG(revenue, 1, 0) makes the first month's change equal its
entire revenue, and a chart will read that as infinite growth from
nothing. NULL is usually the truthful answer for a month with no
predecessor: not zero, but unknown.
Your task: return mon,
revenue, and change against the previous row,
leaving the first month's change as NULL. Order by mon.
query.sql
PostgreSQLCtrl↵ to run
Hint
revenue - LAG(revenue) OVER (ORDER BY mon) AS change. No third argument — the first month's change should stay NULL.
Output
05
To do
LEAD is LAG pointed the other way: the value
from the next row, with the same optional
n and default arguments. The last row gets NULL.
LEAD(revenue) OVER (ORDER BY mon)
It is less common than LAG and genuinely useful for two things: laying
out a step of a journey next to the step that followed it, and
measuring the interval to the next event — the shape behind
"how long until they came back".
Your task: return mon,
revenue, and the following row's revenue as
next_revenue. Order by mon.
query.sql
PostgreSQLCtrl↵ to run
Hint
LEAD(revenue) OVER (ORDER BY mon) AS next_revenue.
Output
06
To do
LAG plus PARTITION BY answers a question that
is genuinely hard otherwise: how long between one customer's
orders?
o.ordered_at - LAG(o.ordered_at) OVER (PARTITION BY o.user_id ORDER BY o.ordered_at)
The partition restarts the reaching-back at each user, so nobody's first
order is compared with someone else's last. Subtracting two
DATEs gives an integer number of days — real dates, from
the Postgres migration, doing real arithmetic.
Each user's first order has no predecessor within their own
partition, so its gap is NULL. Ten users have ordered, so ten NULLs.
Your task: return user_id,
ordered_at, and gap_days since that user's
previous order. Order by user_id, then ordered_at.
query.sql
PostgreSQLCtrl↵ to run
Hint
o.ordered_at - LAG(o.ordered_at) OVER (PARTITION BY o.user_id ORDER BY o.ordered_at) AS gap_days.
Output
07
To do
Every frame so far started at the beginning of the partition. A frame
can also start a fixed number of rows back, and then it
slides — which is what a moving average is.
AVG(revenue) OVER (ORDER BY mon ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
Three rows wide: this one and the two before it. Moving averages exist
to make a noisy series readable — a single spike stops dominating the
shape.
At the start of the series the frame is simply shorter. The first row
averages one value, the second averages two, and only from the third
onwards is it a true three-month average. Postgres does not pad and does
not warn, so a chart's first two points are computed differently from
the rest.
Your task: return mon,
revenue, and a three-month moving average as
ma3, rounded to two decimals. Order by mon.
query.sql
PostgreSQLCtrl↵ to run
Hint
AVG(revenue) OVER (ORDER BY mon ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) goes inside the ROUND.
Output
08
To do
FIRST_VALUE and LAST_VALUE return a column
from the first and last row of the frame. FIRST_VALUE
behaves as everyone expects. LAST_VALUE does not, and the
reason is the frame again.
The default frame ends at CURRENT ROW. So the "last row
of the frame" is the current row, and
LAST_VALUE returns the value you already had — a column
that looks like a copy of another column, which is exactly how the bug
is usually spotted.
-- wrong: returns this row's own amount LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY ordered_at)
-- right: the frame is opened to the end of the partition LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY ordered_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
Many people sidestep it entirely by using
FIRST_VALUE with the ordering reversed, which needs no
frame clause and is harder to get wrong.
Your task: return user_id,
ordered_at, amount, and that user's
most recent order amount as latest_amount
— the same value on every row for a given user. Order by user_id, then
ordered_at.
query.sql
PostgreSQLCtrl↵ to run
Hint
The starter has the bug. Add the frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, inside the same OVER after the ORDER BY.
Output
09
To do
A change in currency is hard to compare across months of different
sizes, so trend reports usually want a percentage. Everything needed is
already here, plus one guard.
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY mon)) / NULLIF(LAG(revenue) OVER (ORDER BY mon), 0), 1)
NULLIF(prev, 0) is doing the real work. A month with no
revenue would otherwise be a division by zero, and in Postgres that is
an error that fails the whole query — not a NULL, and
not a zero. The first month is NULL throughout, because there is
nothing to compare it against.
The 100.0 is a habit rather than a fix here.
revenue is NUMERIC, so 100 would
give the same answer. Integer division only bites when
both operands are integers — which is exactly what
happens the moment you divide one COUNT by another, since
COUNT returns a bigint:
Writing 100.0 everywhere costs nothing and means you never
have to check which case you are in.
Your task: return mon,
revenue, and pct_change against the previous
row, rounded to one decimal place. Order by mon.
query.sql
PostgreSQLCtrl↵ to run
Hint
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY mon)) / NULLIF(LAG(revenue) OVER (ORDER BY mon), 0), 1) AS pct_change.
Output
The monthly trend
To do
The report a finance team actually asks for, and every column of it is a
window function over the same monthly totals.
Your task: one row per month that has
paid revenue, with:
mon — the month, as a DATE on the first of the month
revenue — that month's paid revenue
running — cumulative revenue to date
prev_revenue — the previous row's revenue, NULL for the first
change — revenue minus prev_revenue
pct_change — that change as a percentage of prev_revenue, to one decimal place
Order by mon.
Write the running total's frame out as ROWS. There are no
ties in a monthly series so the default would agree here — which is
exactly why the habit is worth forming somewhere it does not cost you
anything.
query.sql
PostgreSQLCtrl↵ to run
Hint
The CTE is SELECT date_trunc('month', ordered_at)::date AS mon, SUM(amount) AS revenue FROM orders WHERE status = 'paid' GROUP BY date_trunc('month', ordered_at). Then SUM(revenue) OVER (ORDER BY mon ROWS UNBOUNDED PRECEDING), LAG(revenue) OVER (ORDER BY mon), the subtraction, and the NULLIF-guarded percentage.