Conversion
Hermione forwards Harry a message from the product team: “Onboarding conversion fell. Can you find where?”
Onboarding conversion — like, sign-up screen, verify email, that whole flow? We don't track any of that in these tables.
We don't. NOVA doesn't log clicks or screen views — no event table. But we're not blind here: every customer either has placed an order or hasn't, and that's a completely real conversion number. Let's start there.
Conversion rate, stripped down to its simplest form, is just: out of everyone who could have done the thing, what fraction actually did it? For NOVA, “signed up” is a row in customers, and “converted” is having placed at least one order.
Conversion rate = customers who did the thing ÷ customers who could have.
Two counts, then divide. Total customers first:
SELECT COUNT(*) FROM customers;| COUNT(*) |
|---|
| 12 |
Now customers who’ve placed at least one order. orders has one row per order, and a customer can have several — so it’s COUNT(DISTINCT customer_id), not COUNT(*):
SELECT COUNT(DISTINCT customer_id) FROM orders;| COUNT(DISTINCT customer_id) |
|---|
| 10 |
Put them together with a couple of scalar subqueries:
SELECT
(SELECT COUNT(*) FROM customers) AS total_customers,
(SELECT COUNT(DISTINCT customer_id) FROM orders) AS converted,
ROUND(
(SELECT COUNT(DISTINCT customer_id) FROM orders) * 100.0
/ (SELECT COUNT(*) FROM customers),
1
) AS conversion_rate_pct;| total_customers | converted | conversion_rate_pct |
|---|---|---|
| 12 | 10 | 83.3 |
83.3%. So... conversion didn't fall, it's fine?
One overall number can't fall or rise on its own — it's an average of a lot of different customers. If product thinks something dropped, the number is hiding it somewhere. Let's break it apart.
Same idea, but grouped by country. This needs a LEFT JOIN rather than an inner join — a country with customers who never ordered still needs to show up, with zero converted, not disappear entirely:
SELECT
c.country,
COUNT(DISTINCT c.id) AS customers,
COUNT(DISTINCT o.customer_id) AS converted,
ROUND(COUNT(DISTINCT o.customer_id) * 100.0 / COUNT(DISTINCT c.id), 1) AS conversion_rate_pct
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.country
ORDER BY conversion_rate_pct ASC;| country | customers | converted | conversion_rate_pct |
|---|---|---|---|
| France | 1 | 0 | 0 |
| Japan | 1 | 0 | 0 |
| Brazil | 2 | 2 | 100 |
| Germany | 1 | 1 | 100 |
| India | 2 | 2 | 100 |
| UK | 2 | 2 | 100 |
| USA | 3 | 3 | 100 |
There it is. France and Japan sit at a flat 0% — and both happen to be NOVA’s two customers (Chloe and Hana) who never placed a single order. The 83.3% overall number was quietly averaging two dead markets against five healthy ones.
Running…
Try it: Is a country with exactly 1 customer at 0% actually a real trend, or just one unlucky data point?
Run your query to see results here.
Five of NOVA’s seven countries have exactly one customer. France’s “0% conversion” is really just one person who hasn’t ordered yet — wearing a percentage sign doesn’t make it a trend. A rate computed over a sample of one tells you almost nothing about the country; it tells you about that one customer. Before you trust any rate, check the denominator.
Product agrees France and Japan are too small a sample to act on. Show conversion rate by country again, but this time only for countries with more than one customer — enough to actually mean something.
Hint 1
You still need the same LEFT JOIN + GROUP BY shape as above.
Hint 2
Filtering on a condition that only makes sense after grouping (like customer count) is exactly what HAVING is for — not WHERE.
Hint 3
... GROUP BY c.country HAVING COUNT(DISTINCT c.id) > 1 ORDER BY conversion_rate_pct DESC;
Solution
Running…
Once you filter out the single-customer noise, every remaining country converts at 100%. That’s the actual finding: NOVA’s conversion “problem” isn’t a widespread drop — it’s two brand-new, barely-seeded markets that product should either invest in or stop worrying about. A single overall rate would never have told you that.
A conversion rate is only as trustworthy as its denominator. Always break an overall rate apart by a real dimension before believing it went up or down — and use HAVING to drop groups too small to mean anything.