The habit this page teaches
Cards 01–03 design the query; card 04 traces one day through it with real numbers; card 05 names the traps. The closing checklist stays open.
Read the question backwards to design forwards. The output tells you the last stage; each stage's needs force the stage before it into existence. Narrate the stages before typing.
01 The question, decomposed Every phrase in the prompt maps to one pipeline stage.
The prompt (pgexercises)“For each day in August 2012, calculate a rolling average of total revenue over the previous 15 days. Output should contain date and revenue columns, sorted by the date. Remember to account for the possibility of a day having zero revenue.”
| Phrase | Stage it forces |
|---|---|
| “total revenue” | 1 · daily — money per day (the tables only have slots + two prices) |
| “a day having zero revenue” | 2 · calendar + 3 · filled — make missing days exist |
| “rolling average … 15 days” | 4 · rolling — a window frame |
| “each day in August” | 5 · final — filter last, after the window ran |
That mapping is the narration: name the stages out loud before typing, and each CTE gets a name a reader can follow.
Design
02 The pipeline, with whys Five stages; each exists because the next one needs something the data doesn't give you yet.
Pipeline breakdownWhy each stage exists — not just what it does.
RAW DATA ┌───────────────────────┐ bookings ──────▶│ STAGE 1 · daily │ WHY: the question is about facilities ────▶│ join, price each │ revenue, but the tables only │ booking (SUM(CASE │ have slots + two price │ guest/member)), │ columns. First make the │ GROUP BY date │ number the question talks └───────────┬───────────┘ about, at day grain. │ │ PROBLEM: a day nobody booked has no │ rows → no bucket → it doesn't exist. │ Averages over "the last 15 days" │ would silently skip it. │ MANUFACTURED ┌───────────▼───────────┐ generate_series │ STAGE 2 · calendar │ WHY: you can't zero-fill Jul18–Aug31 ───▶│ one row per calendar │ days that aren't there. │ day, bookings or not │ Manufacture the full set. └───────────┬───────────┘ (Starts Jul 18: Aug 1 │ needs 14 days behind it.) │ ┌───────────▼───────────┐ │ STAGE 3 · filled │ WHY: LEFT JOIN keeps every │ calendar LEFT JOIN │ calendar day; coalesce turns │ daily, coalesce(rev,0)│ "unknown" into the 0 the └───────────┬───────────┘ question says it means. │ │ Now: exactly one row per day. │ rows == days. That's the contract │ the next stage depends on. │ ┌───────────▼───────────┐ │ STAGE 4 · rolling │ WHY: "rolling avg over 15 │ AVG(rev) OVER (ORDER │ days" = a frame: 14 back + │ BY date ROWS 14 │ today. ROWS counts rows — │ PRECEDING → CURRENT) │ only valid because stage 3 └───────────┬───────────┘ made rows equal days. │ ┌───────────▼───────────┐ │ STAGE 5 · final │ WHY: the window needed July │ WHERE date >= │ rows to average over, but the │ '2012-08-01' │ output wants August only. │ ORDER BY date │ Compute low, filter high — └───────────┬───────────┘ filter AFTER the window ran. │ ▼ date · revenue, Aug 1–31
Two load-bearing arrowsThe LEFT JOIN is directional on purpose — calendar is the left side, so every day survives even with no bookings match. And the filled → rolling arrow carries the contract: the frame counts rows, and stage 3 is what makes rows equal days.
Implementation
03 The SQL Five stages, four named CTEs — each one testable alone in the pad.
Best practiceThe CTE-first version — the official pgexercises answer does this with a correlated subquery; this is the one-pass rewrite.
rolling 15-day average of revenue, August 2012
with daily as ( -- 1 · revenue per day
select b.starttime::date as date,
sum(b.slots * case when b.memid = 0 then f.guestcost
else f.membercost end) as rev
from cd.bookings b
join cd.facilities f on f.facid = b.facid
group by b.starttime::date
),
calendar as ( -- 2 · every calendar day
select generate_series('2012-07-18'::date, '2012-08-31'::date,
'1 day')::date as date
),
filled as ( -- 3 · zero-fill the empty days
select c.date, coalesce(d.rev, 0) as rev
from calendar c
left join daily d on d.date = c.date
),
rolling as ( -- 4 · the 15-day rolling average
select date,
avg(rev) over (order by date
rows between 14 preceding and current row) as revenue
from filled
)
select date, revenue -- 5 · compute low, filter high
from rolling
where date >= '2012-08-01'
order by date;Why CTEs are the right tool here: each box in the pipeline gets a name (daily, calendar, filled, rolling), and in the pad you can select * from filled to test a stage alone before building the next.
Trace
04 Walkthrough: one day traced Follow Aug 1 — and one empty day, Jul 25 — through every stage with real numbers.
What you'll knowThe input table and output table of every stage — absence becoming zero, and a frame reaching back 15 rows. (Numbers are illustrative; the arithmetic is real.)
STAGE 1 · daily — price each booking, bucket by day IN (bookings ⋈ facilities) — Aug 1's three bookings: ┌────────────┬───────┬───────┬────────────┬───────────┐ │ date │ memid │ slots │ membercost │ guestcost │ ├────────────┼───────┼───────┼────────────┼───────────┤ │ 2012-08-01 │ 0 │ 6 │ 5 │ 10 │ guest → 6×10 = 60 │ 2012-08-01 │ 24 │ 4 │ 5 │ 10 │ member → 4×5 = 20 │ 2012-08-01 │ 11 │ 8 │ 5 │ 10 │ member → 8×5 = 40 └────────────┴───────┴───────┴────────────┴───────────┘ …and NO rows anywhere for 2012-07-25 (nobody booked). OUT — one row per day WITH bookings: ┌────────────┬─────┐ │ 2012-07-24 │ 105 │ │ 2012-08-01 │ 120 │ ← 60 + 20 + 40 └────────────┴─────┘ Jul 25 is NOT a zero row — it's ABSENT. No rows → no bucket. STAGE 2 · calendar — manufacture every day IN — nothing: generate_series invents rows from thin air. OUT — 45 rows, Jul 18 → Aug 31: ┌────────────┐ │ 2012-07-18 │ │ … │ │ 2012-07-25 │ ← exists here; calendars don't skip days │ … │ │ 2012-08-31 │ └────────────┘ STAGE 3 · filled — LEFT JOIN + coalesce IN — calendar (45 rows, left side) + daily (booked days only): calendar.date LEFT JOIN daily.date OUT — 45 rows, one per day, gaps now honest zeros: ┌────────────┬─────┐ │ 2012-07-18 │ 90 │ ← matched daily │ … │ … │ │ 2012-07-25 │ 0 │ ← no match → NULL → coalesce → 0 │ … │ … │ │ 2012-08-01 │ 120 │ ← matched daily └────────────┴─────┘ Contract achieved: rows == days. STAGE 4 · rolling — the frame slides IN — filled (45 rows). For the row 2012-08-01, the frame ROWS BETWEEN 14 PRECEDING AND CURRENT ROW grabs: Jul18 Jul19 Jul20 Jul21 Jul22 Jul23 Jul24 Jul25 90 110 80 100 120 95 105 [0] Jul26 Jul27 Jul28 Jul29 Jul30 Jul31 Aug01 130 85 115 90 100 110 (120) └──────────── 15 rows = 15 days ────────────┘ sum = 1450 · avg = 1450 / 15 = 96.67 OUT — still 45 rows; each carries its own frame's average: ┌────────────┬────────┐ │ 2012-07-18 │ … │ ← July frames are short — fine, │ … │ … │ July rows exist only to FEED │ 2012-08-01 │ 96.67 │ the August frames └────────────┴────────┘ Jul 25's honest 0 drags Aug 1's average down — correct! Without stage 3 the frame would grab Jul 17 instead (16 days back) and the average would be silently high. STAGE 5 · final — keep August IN — rolling (45 rows) OUT — 31 rows: ┌────────────┬────────┐ │ 2012-08-01 │ 96.67 │ ← the row we traced │ 2012-08-02 │ … │ ← frame slid: Jul 19 → Aug 2 │ … │ … │ │ 2012-08-31 │ … │ └────────────┴────────┘
Remember thisAbsence vs zero: after stage 1, Jul 25 isn't “zero” — it's nonexistent. Stages 2–3 convert “the database has nothing to say about this day” into “this day earned 0.” The frame is dumb on purpose: it counts 15 rows backward with no calendar awareness — the earlier stages are what make that dumb count mean something true. And the “rolling” is literal: Aug 2's frame drops Jul 18 off the back and picks up Aug 2.
Pitfalls
05 The traps Off-by-one on the frame, missing-day drift, and the correlated-subquery comparison.
Common mistakesThe three things to say out loud if this shape appears in the pad.
| Trap | The save |
|---|---|
| “15 days” → 15 PRECEDING | Current row counts: 15 days including today = 14 PRECEDING AND CURRENT ROW. Off-by-one is the classic error here. |
| Skipping the zero-fill | ROWS counts rows, not days — a missing day makes the frame silently reach one day too far back. The zero-fill is load-bearing, not cosmetic. |
| Starting the calendar at Aug 1 | Aug 1's frame needs 14 days behind it — the calendar must start Jul 18, and the August filter goes outside the window's CTE. |
Performance tipThe official pgexercises answer computes each day's average with a correlated subquery (a sum re-run per output day, divided by 15). The window version is the one-pass rewrite — “I'd replace that correlated subquery with a frame-clause average” is exactly the line that scores.
Wrap-up
06What good understanding looks like
- You read the question backwards — output → frame → contract (one row per day) → zero-fill → metric — and narrate the stages before typing.
- You can explain absence vs zero: a day with no bookings has no bucket, and stages 2–3 exist to convert absence into an honest 0.
- You write ROWS BETWEEN 14 PRECEDING AND CURRENT ROW for “15 days including today” without hesitating on the 14.
- You test each CTE alone (select * from filled) when something looks wrong, instead of re-reading the whole query.
Was this useful, or did I get something wrong? Email me. I read every reply.