Project 3 — Product Investigation
The VP says onboarding conversion fell.
Fell compared to what? Fell when? Fell by how much?
None of that is in the message. That's the whole assignment — the VP has a feeling, not a query. You have to turn a feeling into a number before you can even agree or disagree with it.
“Onboarding conversion” needs a definition before it needs a query. NOVA has no onboarding flow to instrument, so Harry defines it the way Chapter 08 taught him to — honestly, from the data that exists: a customer “converts” the moment they place their first completed order after signing up.
Harry’s first move: pull total completed orders per quarter and see if any quarter looks weak.
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: 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…
There. Q1 had 6 completed orders, Q2 dropped to 5, Q3 climbed back to 6. Found it — Q2 is where conversion fell.
Did NOVA get the same number of new signups in every quarter?
...no, actually, I have no idea. I never checked.
A raw order count isn’t a conversion rate — it’s a count that goes up when more people sign up and down when fewer do, regardless of how well onboarding is working. Comparing quarter to quarter this way answers a different, less useful question.
Fine — Harry builds an actual rate: completed orders placed in a quarter, divided by new signups in that same quarter.
WITH signups AS (
SELECT
CASE
WHEN signup_date < '2023-01-01' THEN '2022 Q4'
WHEN signup_date < '2023-04-01' THEN '2023 Q1'
WHEN signup_date < '2023-07-01' THEN '2023 Q2'
ELSE '2023 Q3'
END AS quarter,
COUNT(*) AS new_signups
FROM customers
GROUP BY quarter
),
placed 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,
COUNT(*) AS orders_placed
FROM orders
WHERE status = 'completed'
GROUP BY quarter
)
SELECT s.quarter, s.new_signups, p.orders_placed,
ROUND(100.0 * p.orders_placed / s.new_signups, 1) AS conversion_pct
FROM signups s
JOIN placed p ON p.quarter = s.quarter
ORDER BY s.quarter;| quarter | new_signups | orders_placed | conversion_pct |
|---|---|---|---|
| 2022 Q4 | 2 | 1 | 50 |
| 2023 Q1 | 5 | 6 | 120 |
| 2023 Q2 | 3 | 5 | 166.7 |
| 2023 Q3 | 2 | 6 | 300 |
300%? A conversion rate can't be 300%. Something's broken.
Nothing's broken — the SQL ran fine. What's wrong is what you're dividing. Walk me through what ‘orders placed in Q3’ actually contains.
Any completed order placed in Q3... from anyone. Including someone who signed up back in Q1 and finally bought something in Q3.
Exactly. You're counting an old customer's order against this quarter's new signups. The numerator and denominator aren't even talking about the same people.
Every rate has a denominator — make sure it’s counting the same group as the numerator.
A real conversion rate has to follow one group of people: the customers who signed up in a given quarter, and whether those specific people converted. Not orders bucketed by when they happened — orders traced back to the signup cohort that produced them.
Harry keeps it simple and asks: did each customer place their first completed order in the same quarter they signed up, a later one, or never?
Running…
Every rate here is between 0% and 100% — the first sign this metric is actually measuring what it claims to. And there is a real dip: the Q2 2023 cohort converts in-quarter at 66.7%, down from 80% in Q1 and back up to 100% by Q3. Smaller than the raw order counts made it look, but real.
The VP’s next question is always “who, specifically?” Pull every customer who signed up in Q2 2023 (April 1 through June 30), and for each one, show name, signup_date, and a conversion label of ‘same quarter’, ‘later quarter’, or ‘never’.
Hint 1
You need each Q2 customer's first completed order date, if they have one — a LEFT JOIN from customers to orders, grouped per customer, gets you that.
Hint 2
MIN(placed_at) FILTER (WHERE status = 'completed') gives you one date per customer (or NULL if they never converted) without needing a separate subquery.
Hint 3
WHERE c.signup_date >= '2023-04-01' AND c.signup_date < '2023-07-01' narrows to the Q2 cohort; then a CASE on the MIN(...) FILTER(...) expression (NULL → 'never', < '2023-07-01' → 'same quarter', else → 'later quarter') produces the label.
Solution
Running…
Try it: What does that Q2 breakdown actually look like — run it and see which name is dragging the cohort down.
Run your query to see results here.
Three customers, three outcomes: Emma and Farah both converted the same quarter they signed up. Hana Sato never placed a single order. One person is the entire gap between Q2’s 66.7% and a clean 100% — which is exactly why cohort sizes this small deserve a name-level look before anyone writes “conversion fell” in a slide.
The VP wasn’t wrong, exactly — there is a real, if modest, dip in Q2. But the first two attempts to prove it were both broken in ways that would have shipped a wrong number straight into a leadership deck: one ignored the denominator entirely, the other used the wrong one. The fix wasn’t fancier SQL — FILTER and a CASE expression are things you’ve known since Chapter 07 — it was tracking the right group of people all the way through.
Before trusting any rate, name its numerator and its denominator out loud and check they’re describing the same group. A rate over 100%, or one that swings wildly on a handful of people, isn’t a business problem yet — it’s usually a definition problem.