How this rhymes with the rolling average
Cards 01–03 design the query; card 04 traces one facility-month with real arithmetic; card 05 names the traps.
When the question mentions a quantity you can't point to in the schema, one stage of your pipeline exists to build it. The rolling average manufactured missing rows (a calendar); this one manufactures a missing column (the capacity denominator).
01 The question, decomposed Every phrase maps to a stage — including the phrase that removes work.
The prompt (pgexercises)“Work out the utilisation percentage for each facility by month, sorted by name and month, rounded to 1 decimal place. Opening time is 8am, closing time is 8.30pm. You can treat every month as a full month, regardless of if there were some dates the club was not open.”
| Phrase | Stage it forces |
|---|---|
| “utilisation percentage” | needs booked ÷ available — two numbers we must build |
| “for each facility by month” | 1 · monthly — bucket slots by (facility, month) |
| “opening 8am, closing 8.30pm” | 2 · capacity — 12.5 h/day × 2 slots/h = 25 slots/day |
| “treat every month as a full month” | spec words that remove work — no prorating partial months |
| “rounded to 1 dp, sorted by name and month” | 3 · final — divide, round, sort |
Key Insight“Treat every month as full” is the setter excusing you from the hard version — same move as “don't worry about printing a letter if the count is 0” in the surname question. Learn to hear these: they tell you which edge cases NOT to build.
02 The pipeline, with whys Bucket to the question's grain, manufacture the denominator, divide safely.
Pipeline BreakdownWhy each stage exists — and where the missing denominator comes from.
Pipeline Architecture
RAW DATA ┌───────────────────────┐ bookings ──────▶│ STAGE 1 · monthly │ WHY: utilisation needs "slots facilities ────▶│ join for the name, │ booked per facility-month" and │ date_trunc('month'), │ the raw data is per-booking. │ SUM(slots), GROUP BY │ Bucket to the grain the └───────────┬───────────┘ question is asked at. │ │ PROBLEM: "available slots" isn't in │ any table. Months have different │ lengths, so it's not a constant. │ ┌───────────▼───────────┐ │ STAGE 2 · capacity │ WHY: manufacture the missing │ available = 25 × │ denominator. days-in-month = │ days in that month │ (month + 1 month) - month, │ (add a month, │ because Postgres date - date │ subtract the dates) │ = an integer of days. └───────────┬───────────┘ │ ┌───────────▼───────────┐ │ STAGE 3 · final │ WHY: the ratio needs both │ round(100 * slots │ columns to exist first — │ / available::numeric, │ compute low, divide high. │ 1), ORDER BY │ ::numeric dodges integer └───────────┬───────────┘ division (the /2.0 trap!). │ ▼ name · month · utilisation
Remember ThisThe pipeline shape — bucket → manufacture → divide — recurs whenever the question asks for a ratio whose denominator doesn't exist as a column. Spot the pattern and the stages write themselves.
Implementation
03 The SQL Two CTEs and a divide — the official answer uses an inline subquery; this is the named-stage version.
What you'll knowThe CTE-first version, each stage testable alone in the pad.
utilisation percentage per facility per month
with monthly as ( -- 1 · slots booked per facility-month
select f.name,
date_trunc('month', b.starttime) as month, -- keep the timestamp (see traps)
sum(b.slots) as slots
from cd.bookings b
join cd.facilities f on f.facid = b.facid
group by f.facid, f.name, date_trunc('month', b.starttime)
),
capacity as ( -- 2 · manufacture the denominator
select name, month, slots,
25 * ((month + interval '1 month')::date - month::date) as available
from monthly
)
select name, month, -- 3 · divide, round, sort
round(100 * slots / available::numeric, 1) as utilisation
from capacity
order by name, month;Verification
04 Walkthrough: one row traced Tennis Court 1, August 2012 — input and output tables for every stage.
Business ContextThe input and output of each stage. (Numbers illustrative; arithmetic real.)
Stage 1 — Bucket to facility-month grain
STAGE 1 · monthly — bucket bookings to facility-month grain
IN (bookings ⋈ facilities) — August rows for this court:
┌─────────────────────┬────────────────┬───────┐
│ starttime │ name │ slots │
├─────────────────────┼────────────────┼───────┤
│ 2012-08-01 09:30:00 │ Tennis Court 1 │ 3 │ date_trunc('month')
│ 2012-08-01 14:00:00 │ Tennis Court 1 │ 2 │ maps ALL of these
│ 2012-08-02 10:00:00 │ Tennis Court 1 │ 3 │ to 2012-08-01 —
│ … │ … │ … │ the month bucket key
└─────────────────────┴────────────────┴───────┘
OUT — one row per facility-month:
┌────────────────┬────────────┬───────┐
│ name │ month │ slots │
├────────────────┼────────────┼───────┤
│ Tennis Court 1 │ 2012-07-01 │ 483 │
│ Tennis Court 1 │ 2012-08-01 │ 580 │ ← our traced row
│ Tennis Court 1 │ 2012-09-01 │ 591 │
└────────────────┴────────────┴───────┘
Stage 2 — Manufacture the denominator
STAGE 2 · capacity — manufacture the denominator
IN — the 2012-08-01 row above.
days in month: ('2012-08-01' + 1 month) = '2012-09-01'
'2012-09-01' - '2012-08-01' = 31 (date - date = int days)
available: 25 slots/day × 31 days = 775
OUT — same rows, one new column:
┌────────────────┬────────────┬───────┬───────────┐
│ name │ month │ slots │ available │
├────────────────┼────────────┼───────┼───────────┤
│ Tennis Court 1 │ 2012-08-01 │ 580 │ 775 │
└────────────────┴────────────┴───────┴───────────┘
Feb would get 25×29=725 (2012 is a leap year), Sep 25×30=750 —
the add-a-month trick handles all of that for free.
Stage 3 — Divide, round, sort
STAGE 3 · final — divide, round, sort
IN — the row above.
100 × 580 / 775::numeric = 74.8387… → round(…, 1) = 74.8
(without ::numeric: 58000 / 775 = 74 — integer division
silently eats the decimals BEFORE round ever runs!)
OUT:
┌────────────────┬────────────┬─────────────┐
│ name │ month │ utilisation │
├────────────────┼────────────┼─────────────┤
│ Tennis Court 1 │ 2012-08-01 │ 74.8 │
└────────────────┴────────────┴─────────────┘
Pitfalls
05 The traps Days-in-month, integer division (again), deriving the 25, and the month bucket key.
Common MistakesThe four things to say out loud if this shape appears in the pad.
| Trap | The save |
|---|---|
| Hardcoding 30 or 31 days | (month + interval '1 month')::date - month::date — date minus date is an integer of days; leap years and short months come free |
| Casting the output column | date_trunc returns a timestamp, and checkers (pgexercises included) compare values exactly — casting month to ::date fails the check even though the logic is right. Cast where you compute (the day count needs dates), not where you display. Caught live on 2026-07-03. |
| Integer division (again) | 580/775 = 0 in integer math — cast one side ::numeric before dividing; round() can't recover digits already thrown away |
| The 25 | Derive it out loud: 8:00→20:30 is 12.5 hours, slots are half-hours, so 25/day. Interviewers like the derivation more than the number |
| Grouping by raw date | date_trunc('month', starttime) collapses all of August onto Aug 1 — that value is the month bucket's key |
Common MistakeThis is integer division's second appearance (first: hours = sum(slots)/2.0). Any time two integer columns meet a /, one side must become non-integer first. Make it a reflex-level check.
06What good understanding looks like
- You hear a quantity that isn't in the schema (“available slots”) and immediately assign it a pipeline stage of its own.
- You write the days-in-month idiom — add a month, subtract the dates — without reaching for a hardcoded 30.
- You check every division between columns for the integer trap before running, not after the numbers look wrong.
- You notice spec words that remove work (“treat every month as full”) and skip the edge cases they excuse.
Was this useful, or did I get something wrong? Email me. I read every reply.