Final Project — The NOVA Investigation
NOVA's growth team believes something changed in the last quarter. Find out what.
That's the whole brief? No table hint, no metric, not even which direction — up or down?
That's the whole brief. Same four tables you've had since Chapter 00. No prescribed concepts this time — just a claim and a database. Find the evidence yourself, and be ready to show your work.
A long time ago Harry opened NOVA’s database for the first time and asked Hermione, more or less, “okay… where’s the data?” Every idea since then — WHERE, GROUP BY, joins, subqueries, CTEs, window functions — was really just building toward being able to answer a question like this one: vague, high stakes, and completely unscoped.
No one is going to tell Harry which table to start with. So he starts where every one of the last three projects taught him to start: get one honest number on the board before chasing anything clever.
“Last quarter” means 2023 Q3, the most recent one NOVA has data for. Before asking why anything changed, Harry checks whether revenue actually moved at all, quarter by quarter, completed orders only.
Table: customers
| Column Name | Type |
|---|---|
| id | INTEGER |
| name | TEXT |
| country | TEXT |
| TEXT | |
| phone | TEXT |
| signup_date | DATE |
id is primary key. phone is nullable.
Table: products
| Column Name | Type |
|---|---|
| id | INTEGER |
| name | TEXT |
| category | TEXT |
| price | DECIMAL(10,2) |
id is primary key.
Table: orders
| Column Name | Type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| product_id | INTEGER |
| quantity | INTEGER |
| amount | DECIMAL(10,2) |
| status | TEXT |
| placed_at | DATE |
id is primary key. customer_id is foreign key -> customers.id. product_id is foreign key -> products.id. amount is quantity * unit price at time of order. status is completed, pending, refunded, or cancelled.
Running…
Revenue isn’t flat: $199.99, then $439.44, then a drop to $258.45, then back up to $401.97. There’s real movement here — the growth team wasn’t imagining things. The question is what “the last quarter” actually looked like relative to the one before it, precisely.
Eyeballing four numbers works when there are four numbers. Harry wants the actual quarter-over-quarter change, and this time he reaches straight for the tool built for exactly this in Chapter 07: LAG.
WITH quarterly AS (
SELECT
CASE
WHEN placed_at < '2023-01-01' THEN '2022 Q4'
WHEN placed_at < '2023-04-01' THEN '2023 Q1'
WHEN placed_at < '2023-07-01' THEN '2023 Q2'
ELSE '2023 Q3'
END AS quarter,
amount
FROM orders
WHERE status = 'completed'
),
totals AS (
SELECT quarter, SUM(amount) AS revenue
FROM quarterly
GROUP BY quarter
)
SELECT quarter, revenue,
LAG(revenue) OVER (ORDER BY quarter) AS prev_quarter_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY quarter)) / LAG(revenue) OVER (ORDER BY quarter), 1) AS pct_change
FROM totals
ORDER BY quarter;| quarter | revenue | prev_quarter_revenue | pct_change |
|---|---|---|---|
| 2022 Q4 | 199.99 | NULL | NULL |
| 2023 Q1 | 439.44 | 199.99 | 119.7 |
| 2023 Q2 | 258.45 | 439.44 | -41.2 |
| 2023 Q3 | 401.97 | 258.45 | 55.5 |
There it is: revenue fell 41.2% in Q2, then jumped 55.5% in Q3. “Something changed last quarter” is technically true of Q2 and Q3 — the growth team is almost certainly reacting to the Q3 rebound, so that’s the one Harry needs to explain: what actually drove revenue back up.
Before chasing a cause, rule out the boring explanation: maybe Q3 just had more orders, not better ones. Extend the quarter-over-quarter view to also show orders (count of completed orders) and avg_order_value for each quarter, alongside revenue and pct_change.
Hint 1
COUNT(*) and AVG(amount) can sit right next to SUM(amount) in the same GROUP BY — you're not adding a join or a new grain, just more aggregates per quarter.
Hint 2
Compute all four aggregates (count, sum, avg) inside the same CTE that currently only computes SUM(amount), then apply LAG/the pct_change math on top exactly as before.
Hint 3
SELECT quarter, COUNT(*) AS orders, SUM(amount) AS revenue, ROUND(AVG(amount), 2) AS avg_order_value, then LAG(...) OVER (ORDER BY quarter) on the revenue column, same as the demonstrated query.
Solution
Running…
Q3 had 6 completed orders at a $66.99 average — basically the same order count as Q1’s 6, and only slightly more than Q2’s 5. No dramatic volume story. Whatever changed, it wasn’t simply “more people bought things.” Time to look at what they bought.
Same conditional-aggregation pattern from Chapter 09’s pivot pattern, applied to Q2 versus Q3: revenue per category, side by side.
SELECT p.category,
SUM(CASE WHEN o.placed_at < '2023-07-01' THEN o.amount ELSE 0 END) AS q2_revenue,
SUM(CASE WHEN o.placed_at >= '2023-07-01' THEN o.amount ELSE 0 END) AS q3_revenue,
SUM(CASE WHEN o.placed_at >= '2023-07-01' THEN o.amount ELSE 0 END)
- SUM(CASE WHEN o.placed_at < '2023-07-01' THEN o.amount ELSE 0 END) AS change
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE o.status = 'completed' AND o.placed_at >= '2023-04-01'
GROUP BY p.category
ORDER BY change DESC;| category | q2_revenue | q3_revenue | change |
|---|---|---|---|
| Audio | 0 | 209.97 | 209.97 |
| Accessories | 44.97 | 64 | 19.03 |
| Computing | 148.99 | 128 | -20.99 |
| Wearables | 29.99 | 0 | -29.99 |
| Home | 34.5 | 0 | -34.5 |
Audio went from zero to $209.97. That's the whole story, isn't it — everything else roughly cancels out.
Careful with ‘zero.’ Zero in Q2, sure. Was it zero before that too?
SELECT
CASE
WHEN o.placed_at < '2023-04-01' THEN '2023 Q1'
WHEN o.placed_at < '2023-07-01' THEN '2023 Q2'
ELSE '2023 Q3'
END AS quarter,
SUM(o.amount) AS audio_revenue
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE p.category = 'Audio' AND o.status = 'completed' AND o.placed_at >= '2023-01-01'
GROUP BY quarter
ORDER BY quarter;| quarter | audio_revenue |
|---|---|
| 2023 Q1 | 129.98 |
| 2023 Q3 | 209.97 |
Q2 doesn’t even appear in that result — no Audio order was placed at all. So this isn’t a brand-new category launching in Q3; it’s a category that sold reasonably well in Q1, went completely silent for a quarter, and came back stronger than before. That’s a different, more specific claim than “Audio is growing,” and it’s the one the data actually supports.
Before pinning the whole Q2 dip on Audio going quiet, check the rest of the board. Run the same category pivot for Q1 versus Q2 2023 — category, q1_revenue, q2_revenue, and change — to see whether Q2’s drop was really about Audio alone, or several categories cooling off at once.
Hint 1
This is the same shape as the Q2-vs-Q3 pivot you just saw — same two conditional SUMs, just with the date boundary and range shifted back one quarter.
Hint 2
The boundary between the two periods is 2023-04-01; restrict the WHERE clause to orders from 2023-01-01 onward so quarters before Q1 don't leak in.
Hint 3
SUM(CASE WHEN o.placed_at < '2023-04-01' THEN o.amount ELSE 0 END) AS q1_revenue, SUM(CASE WHEN o.placed_at >= '2023-04-01' THEN o.amount ELSE 0 END) AS q2_revenue, filtered to o.placed_at >= '2023-01-01' AND o.placed_at < '2023-07-01'.
Solution
Running…
Try it: Run that Q1-vs-Q2 pivot yourself — was Q2's dip really just Audio, or did more than one category go quiet?
Run your query to see results here.
Wearables fell further than Audio did ($199.99 → $29.99, a $170 drop) — Q2 was a soft quarter for more than one category, not an Audio-only story. But Computing did the opposite, jumping from $0 to $148.99. Q2 wasn’t “everything fell”; it was categories rotating, with the net effect landing negative. Audio’s Q3 comeback is real and it’s the single biggest swing in either direction — but it’s one piece of a messier picture, not the whole explanation by itself.
One question left: did Q3’s Audio revenue come from brand new customers discovering NOVA, or from existing customers finally buying into a category they’d ignored? Chapter 07’s ROW_NUMBER answers this directly — a customer’s first-ever completed order gets order_seq = 1; anything after that is a returning purchase.
Running…
Three Audio orders in Q3, three different stories: Julia Souza and Kevin Zhang bought Aurora Earbuds and the Nova Speaker Mini as their very first NOVA purchase — both brand new customers. Emma Wilson, on her fourth completed order, finally bought into Audio after three purchases in other categories. Two new customers, one loyal one branching out.
Zoom back out from Audio specifically: for all completed orders in Q3 2023, break revenue down by customer_type (‘new customer’ vs ‘returning customer’, using the same first-order-ever logic). Return customer_type, orders, and revenue, so the growth team can see whether Q3’s recovery as a whole leaned on new acquisition or on existing customers.
Hint 1
You already have the exact building block for this — the ordered CTE with ROW_NUMBER() PARTITION BY customer_id that labeled the Audio orders. Reuse that shape without the category filter.
Hint 2
Filter to o.placed_at >= '2023-07-01' (Q3) without joining to products at all this time, since you're aggregating across every category, not just one.
Hint 3
GROUP BY the CASE WHEN order_seq = 1 THEN 'new customer' ELSE 'returning customer' END expression, with COUNT(*) and SUM(amount) alongside it.
Solution
Running…
Put it all together, and the growth team gets an actual answer instead of a vibe:
- Revenue really did swing — down 41.2% in Q2, up 55.5% in Q3 — and it wasn’t driven by order volume, which stayed roughly flat all year.
- Q2’s dip wasn’t one category’s fault: Wearables and Audio both went quiet while Computing picked up, netting out negative.
- Q3’s single biggest swing was Audio going from silent to $209.97 — a comeback, not a launch, since it had sold $129.98 back in Q1 before disappearing in Q2.
- Company-wide, Q3’s recovery leaned more on returning customers ($271.99) than new ones ($129.98) — but within Audio specifically, two of the three buyers were brand-new customers, and the third was a loyal customer expanding into a category she hadn’t tried before.
Every one of those four bullets is a query Harry can point to, not an opinion. That’s the actual deliverable of an investigation like this — not a single dramatic “aha,” but a chain of evidence solid enough that someone else could rerun every step and get the same answer.
A finding is only as good as the query someone else can rerun to check it.
It's strange — none of that used anything I didn't already know. Window functions, CASE, a couple of CTEs. Nothing new.
That was always the point. You didn't need new syntax. You needed to know which question to ask first, and enough judgment to doubt your own first answer. That's not a SQL skill — that's just thinking clearly with a database attached.
A real investigation is a chain of small, checkable queries, not one clever one — confirm there’s a signal, quantify it, break it down until you find where it concentrates, then verify who or what is actually behind it. Every technique in this course was just preparation for being able to run that chain yourself, on a question nobody scoped for you.