Sign in to open this module
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.
Checking your account…
Capstone: Your First Week
A real analytics job, in fifteen queries
No lesson in this module and no new syntax — you already have all of it. What you have not had is the job: a database nobody documented, people who ask for numbers in their own words rather than in columns, and a deadline that does not care which clause it needs. Fourteen requests land in your inbox across the week. Friday morning, the board pack goes out with your name on it.
You have joined Northwind Analytics
Read this once before you start. It is the whole of the handover you are going to get, which is also true of the real thing.
Northwind Analytics
A small B2B SaaS product. Twelve customers, a handful of plans, a catalogue of seats, add-ons and services. Small enough that every number can be checked by hand — which is exactly why a wrong one is unforgivable here.
Data Analyst — first week
The first one they have hired. Nobody before you wrote a query against this database, so there is no library of trusted SQL to copy from and no colleague to sanity-check a total against.
Board meeting, Friday 09:00
Everything below feeds it. The requests arrive in the order they were sent, and the last one is the pack itself.
Who is asking
Five people, and none of them will describe what they want in SQL. That translation is the job — and so is noticing what a request does not say. "Revenue" does not mention refunds. "Our customers" does not say whether the churned ones count. Both decisions are yours, and both are wrong in one direction if you do not make them deliberately.
Rachel Nkomo · CEO
Asks short questions with large consequences. Wants the board pack, and hates a blank cell.
Priya Raman · Head of Finance
Every number she gets is checked against the bank. If a total is wrong she will find it.
Marcus Bell · Growth
Signups, funnels, referrals. Comfortable with percentages, which is its own hazard.
Dani Okafor · Customer Success
Wants lists of actual people she can email, not aggregates.
Sam Iqbal · Product
Reviewing the catalogue. Interested mainly in the things that are not selling.
What the last engineer left you
Five tables and no documentation, which is the normal amount. Open the schema browser below the queue to see the columns; these are the things you would only find out by getting a number wrong first.
Handover notes
Not every order is money. orders.status
holds paid, refunded and pending.
Only one of those is in the bank, and the difference is 60.00 across
three rows — small enough to slip through, large enough for Priya to
notice.
Blanks are real. users.company is empty
for individuals and subscriptions.cancelled_on is empty
while a subscription is still running. In both cases the absence
is the information, and = NULL will never find it.
People repeat. events has one row per
thing that happened, so the same person appears many times. Counting
rows where you meant to count people is the most common way a funnel
ends up over 100%.
Quiet periods leave no trace. A month in which nothing
was bought produces no rows at all, so it cannot appear in a
GROUP BY — and a chart drawn from that quietly closes the
gap. Priya has been burned by this before; she raises it on Thursday.
The one habit worth carrying into every ticket
Almost nothing that goes wrong here raises an error. A join that
matches twice inflates a total; a filter in the wrong clause deletes
rows; a missing DISTINCT turns one buyer into three. All of
them return a plausible number and none of them complain.
So say what the answer should be before you run the query, even roughly. There are 24 orders and 21 of them are paid. There are 12 users. Paid revenue is 1891.00 and every split of it has to add back up to that. If a result disagrees with a number you already know, the result is wrong, not the number.
Loading the tables…
To do
Dani Okafor · Customer Success
"Morning, and welcome. Can you send me the account list? Everyone who's still with us — name, country, what plan they're on and when they joined. Oldest first, I read it top down."
The gentlest ticket you will get all week, and it still contains a
decision: "still with us" is not a column. Look at
users and work out which one Dani means.
Deliver: name, country,
plan and signup_date for every active user,
oldest signup first.
is_active is a real BOOLEAN, so WHERE is_active is enough — no = true needed. Order by signup_date ascending.
To do
Priya Raman · Head of Finance
"Three numbers for the top of the board pack: how many orders we've taken, what they came to, and the average order value. All time is fine. I need it to match the bank, so be careful."
"Match the bank" is the whole ticket. Twenty-four rows sit in
orders and not all of them are money in the account — look
at what status holds before you total anything. Money that
was refunded left again; money that is pending never arrived.
Deliver: one row — orders,
revenue, aov, the last rounded to 2 decimal
places.
COUNT(*) AS orders, SUM(amount) AS revenue, ROUND(AVG(amount), 2) AS aov, all filtered to WHERE status = 'paid'.
To do
Marcus Bell · Growth
"Need our 2023 signup curve for the board deck — how many people joined each month. Just 2023, chronological."
Signup dates are real DATE values, so a month is
date_trunc away. Cast the result to ::date
rather than leaving it a timestamp — a chart axis reading
2023-03-01 00:00:00 looks like something went wrong even
when nothing did.
Deliver: mon and signups for
each month of 2023 that had any, ordered by month.
Eleven rows come back, not twelve. Hold that thought — Priya's Thursday ticket is about exactly the month that is missing.
date_trunc('month', signup_date)::date AS mon and COUNT(*) AS signups, grouped by the same date_trunc expression.
To do
Dani Okafor · Customer Success
"Following up on yesterday — some of those accounts have no company against them, so I can't tell whether they're a business or someone trying it out. Can you pull the ones with nothing in the company field? I'll chase them."
company is nullable and five rows use that. The obvious
spelling — WHERE company = NULL — returns no rows, no
error and no warning, which is the most expensive silent failure in the
language. NULL is not a value to compare against; it is the absence of
one.
Deliver: user_id, name,
country and plan for every user with no
company recorded, by user_id.
WHERE company IS NULL. = NULL is never true, not even for a NULL.
To do
Priya Raman · Head of Finance
"The board will ask where the money comes from. Split the revenue by product category — licences, add-ons, services — with the order count next to it. Biggest first."
The category lives on products and the money lives on
orders, so this is the first ticket that needs a join. The
paid-only rule from Monday still applies and will apply to every revenue
number this week — it does not stop being true because the query got
harder.
Deliver: category, orders,
revenue, highest revenue first.
JOIN products p ON p.product_id = o.product_id, WHERE o.status = 'paid', then COUNT(*) AS orders and SUM(o.amount) AS revenue grouped by p.category.
To do
Rachel Nkomo · CEO
"Who are our five biggest customers by spend? I want their name, country and company for the board. If we don't have a company on file just put Individual — don't send me blanks, it looks like the data's broken."
Two things worth noticing. The ranking is over money that actually arrived, so the paid filter belongs here too. And Rachel's last sentence is a real requirement: an empty cell in a board pack starts an argument about the data instead of about the customers, so the NULL gets a label rather than a blank.
Deliver: name, country,
company, orders and revenue for
the top five spenders, biggest first, ties broken by name.
COALESCE(u.company, 'Individual') AS company. Join with ON o.user_id = u.user_id AND o.status = 'paid' (or filter in a WHERE — an inner join makes no difference here), then COUNT(*) AS orders and SUM(o.amount) AS revenue.
To do
Sam Iqbal · Product
"I'm reviewing the catalogue for next quarter. Can I get every product with what it's earned us? Every product, please — the ones earning nothing are the entire reason I'm asking."
Sam has anticipated the mistake, and it is the one worth internalising: an inner join answers "products that sold" and silently drops the rest, so the product with no orders — the one the review exists to find — never appears. That is not a wrong number, it is a missing row, and nothing in the output can tell you it is gone.
A product with no matching order gets NULLs from the join, and
SUM over no rows is NULL rather than zero, so that needs
turning into the number it represents.
Deliver: name, category,
units and revenue for all eight products,
highest revenue first, ties broken by name.
LEFT JOIN orders o ON o.product_id = p.product_id AND o.status = 'paid' — both conditions in the ON, or the WHERE deletes the unsold product again. Then COALESCE(SUM(o.quantity), 0) AS units and COALESCE(SUM(o.amount), 0) AS revenue.
To do
Marcus Bell · Growth
"We're thinking about paying for referrals. Before I put a number on it — who has actually referred anyone, and how many? Most first."
users.referred_by points at another row in
users, so the referrer and the referred person are both in
the same table. Join it to itself under two different aliases and the
self-reference stops being confusing: one alias is the person who
joined, the other is whoever sent them.
Deliver: referrer (their name) and
referred (how many people they brought in), most first,
ties broken by name.
JOIN users r ON r.user_id = u.referred_by — u is the person who joined, r is their referrer. COUNT(*) AS referred. The inner join drops anyone who referred nobody, which is what "who has actually referred anyone" asks for.
To do
Priya Raman · Head of Finance
"One more before you go. MRR by plan — how many subscriptions we have on each plan and what they're worth per month. Live ones only, obviously. Cancelled subscriptions don't pay us."
"Live" is again not a column. subscriptions.cancelled_on
holds a date when someone cancelled and nothing at all while they are
still paying, so the absence of a date is the status —
the same NULL logic as Dani's Tuesday ticket, doing real work.
Deliver: plan, subs and
mrr for live subscriptions only, biggest MRR first.
WHERE cancelled_on IS NULL, then COUNT(*) AS subs and SUM(mrr) AS mrr grouped by plan.
To do
Marcus Bell · Growth
"Rachel's going to ask how many people who sign up actually get value out of the product. Three steps: they sign up, they activate, they create a report. How many reach each one, and what percentage of signups is that?"
events holds one row per thing that happened, and a person
can do the same thing more than once — Ada created two reports. Count
rows and she is two people. Count distinct users and she is one, which
is what a funnel means.
The denominator is the first step's user count, and it is the same number for all three rows.
Deliver: step, users and
pct_of_signups to one decimal place, for the three steps
only, widest step first.
Worth knowing before you report it: "75% activate" describes an average of nine people, and none of them is 75% activated. It is a fine headline and a bad basis for a decision at this sample size.
COUNT(DISTINCT e.user_id) AS users. For the percentage, divide it by a scalar subquery: ROUND(100.0 * COUNT(DISTINCT e.user_id) / (SELECT COUNT(DISTINCT user_id) FROM events WHERE event_name = 'signup'), 1).
To do
Priya Raman · Head of Finance
"Monthly revenue, first order to last, for the trend chart. And I mean every month in that window — last year someone sent me a chart with the quiet months quietly missing and I had to correct myself in the meeting. Zero is a number. Show me the zeros."
The obvious query is one line of work and it is what Priya is warning you about:
SELECT date_trunc('month', ordered_at)::date AS mon, SUM(amount)
FROM orders WHERE status = 'paid'
GROUP BY 1;
Sixteen rows, every figure in them correct. But the first paid order is
January 2023 and the last is September 2024 — a span of
21 months. Five contain no paid order at all, so
GROUP BY has nothing to build a row from and they simply do
not exist. Plot that and the line runs straight from October to December
as though November were unremarkable rather than zero.
A GROUP BY can only ever return groups that already contain
a row, so the missing months have to come from somewhere other than the
data: generate the series, take the bounds from the data so the report
cannot go stale, and hang the revenue off it with an outer join.
Where the paid filter goes
Both join conditions belong in the ON. Move
o.status = 'paid' into a WHERE and it runs
after the padding — NULL is not 'paid', so every month
you just went to the trouble of generating is deleted again and you
are back to sixteen rows.
Deliver: mon and revenue for
all 21 months, 0 where there was none, ordered by month.
ON date_trunc('month', o.ordered_at)::date = m.mon AND o.status = 'paid', then COALESCE(SUM(o.amount), 0) AS revenue.
To do
Rachel Nkomo · CEO
"Priya showed me the monthly line. What I actually want next to it is revenue to date — the cumulative number, so I can see where we stand rather than how one month went."
A running total over the months you just built. The monthly figures have to exist as rows before they can accumulate, so the aggregate goes in a CTE and the window runs over its output.
SUM(revenue) OVER (ORDER BY mon ROWS UNBOUNDED PRECEDING)
Write the frame out rather than leaving it to the default. There are no
ties in a month column so RANGE would agree here, which
makes this a free place to build the habit for a column where it would
not.
Deliver: mon, revenue and
running for all 21 months, ordered by month.
SUM(revenue) OVER (ORDER BY mon ROWS UNBOUNDED PRECEDING) AS running.
To do
Priya Raman · Head of Finance
"Last thing today: put month-on-month change on the revenue line, one decimal. And whatever you do, don't print 0% for a month we can't calculate — I'd rather see a blank I can explain than a number I can't."
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY mon))
/ NULLIF(LAG(revenue) OVER (ORDER BY mon), 0), 1)Priya's warning is the interesting half of this ticket. Filling the gaps put five zeros in the column, and a zero has two different consequences depending on which side of the division it lands:
A month that falls to zero
Correct, and exactly what happened. Three months do this.
A month that follows a zero
The denominator is zero. Growth from nothing is not a percentage, so the honest answer is no answer.
Without NULLIF this does not return a wrong number
It returns nothing. Division by zero aborts the whole statement in Postgres, so one empty month means the entire report fails. This is the only mistake in the week that announces itself.
Deliver: mon, revenue and
pct_change to one decimal place, all 21 months, ordered by
month.
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY mon)) / NULLIF(LAG(revenue) OVER (ORDER BY mon), 0), 1) AS pct_change.
To do
Rachel Nkomo · CEO
"Board's in an hour. Someone will point at a spike and ask what drove it — I don't want to be guessing. Best-selling product in each month, with what it made."
Top-one-per-group, in two steps: aggregate to a product per month, then
number the products within each month and keep number one. The
numbering has to restart every month, which is what
PARTITION BY does — without it you get the best product
overall, once.
Group by the written-out expression rather than
GROUP BY 1, 2: a positional GROUP BY does not
match the expression the window's PARTITION BY refers to,
and Postgres rejects it with "column must appear in the GROUP BY
clause".
Deliver: for each month that has paid revenue,
mon, the name of its highest-earning product
and that product's rev. Ties broken by name, ordered by
month.
Sixteen rows here, not 21 — an empty month has no best product, and inventing one for it would be worse than the gap.
per is SELECT date_trunc('month', o.ordered_at)::date AS mon, p.name, SUM(o.amount) AS rev FROM orders o JOIN products p ON p.product_id = o.product_id WHERE o.status = 'paid' GROUP BY date_trunc('month', o.ordered_at), p.name. Then ROW_NUMBER() OVER (PARTITION BY mon ORDER BY rev DESC, name) AS rn, and WHERE rn = 1.
Fri 09:00 — the board pack
To doRachel Nkomo · CEO
"Everything in one table, please — I'm not flipping between five exports in front of the board. Every month since we started, what we made, how many orders, how many actual customers, where we stand cumulatively, and the month-on-month move. If a month was quiet I still want the row."
The week, assembled. Every column of this is a ticket you have already done, and the only new work is putting them in one query without letting any of them break the others.
Deliver: one row for every month from the first month containing a paid order to the last — including the quiet ones — with:
mon— the first day of the month, as aDATEorders— paid orders that month,0if nonebuyers— distinct users who bought that month,0if nonerevenue— paid revenue that month,0rather than NULL if nonerunning— cumulative revenue including this monthpct_change— change against the previous month as a percentage, to one decimal place, NULL where it cannot be computed
Order by mon.
Reconcile it before you send it
Nearly every failure this week was silent — a join that duplicated, a filter that deleted, a count that counted the wrong thing. None of them raises an error and all of them move a total. So say what the numbers should be before you look: 21 months of span, 21 paid orders, 1891.00 of revenue, and a final running total equal to that. Three numbers you can hold in your head, and any one of them being wrong points at something specific.
Four ways to get this wrong, all of them seen this week. The paid filter
belongs in the ON, or the quiet months you generated are
deleted again. Count a column rather than the rows, or those months report
one order each. Count buyers with DISTINCT, or somebody who
ordered twice is two customers. And guard the percentage's denominator —
three months fall to zero, and dividing by one of them fails the whole
query rather than one row.
bounds takes MIN and MAX of date_trunc('month', ordered_at) over paid orders. months is generate_series(bounds.a, bounds.b, INTERVAL '1 month'). monthly LEFT JOINs orders to months with the paid condition in the ON, using COUNT(o.order_id), COUNT(DISTINCT o.user_id) and COALESCE(SUM(o.amount), 0). Then SUM(revenue) OVER (ORDER BY mon ROWS UNBOUNDED PRECEDING) and the NULLIF-guarded percentage.