Retention
Harry’s manager asks something that sounds simple: “Are the customers we got last month still around?”
"Still around" — like, still logged in? We don't track sessions either.
Not logged in — still buying. For an e-commerce table, 'still around' means 'ordered again.' If someone bought in May and never shows up in orders again, they're not around anymore, whatever their login status says.
Retention, this way: take everyone who ordered in some month, and ask what fraction of them also ordered in the very next month. That’s month-over-month retention — the most common flavor there is, and every other kind (weekly, day-30, whatever) is the same idea with a different window.
Retention = people active this period who are also active next period, divided by everyone active this period.
Start with which customer ordered in which month — the same CAST + SUBSTR trick from the revenue lesson, deduplicated with DISTINCT since one customer can order several times in the same month:
SELECT DISTINCT customer_id, SUBSTR(CAST(placed_at AS TEXT), 1, 7) AS month
FROM orders
ORDER BY customer_id, month;Comparing month strings directly gets messy fast — “the month after 2023-12” isn’t just adding 1, and this codebase deliberately avoids relying on date arithmetic since it doesn’t behave the same across every SQL engine. Instead, number the months in order using DENSE_RANK — a tool you already know from ranking. Then “next month” is just month_index + 1, plain integer math:
WITH customer_months AS (
SELECT DISTINCT customer_id, SUBSTR(CAST(placed_at AS TEXT), 1, 7) AS month
FROM orders
),
months AS (
SELECT month, DENSE_RANK() OVER (ORDER BY month) AS month_index
FROM (SELECT DISTINCT month FROM customer_months) m
)
SELECT cm.customer_id, mo.month_index
FROM customer_months cm
JOIN months mo ON mo.month = cm.month
ORDER BY mo.month_index, cm.customer_id;Now self-join that customer/month-index list to itself: match each row to the same customer one month index later. A LEFT JOIN, so customers who didn’t come back still show up — with a NULL match, not a missing row:
WITH customer_months AS (
SELECT DISTINCT customer_id, SUBSTR(CAST(placed_at AS TEXT), 1, 7) AS month
FROM orders
),
months AS (
SELECT month, DENSE_RANK() OVER (ORDER BY month) AS month_index
FROM (SELECT DISTINCT month FROM customer_months) m
),
customer_month_index AS (
SELECT cm.customer_id, mo.month_index
FROM customer_months cm
JOIN months mo ON mo.month = cm.month
)
SELECT
a.month_index AS starting_month,
COUNT(DISTINCT a.customer_id) AS active_customers,
COUNT(DISTINCT b.customer_id) AS retained_next_month,
ROUND(COUNT(DISTINCT b.customer_id) * 100.0 / COUNT(DISTINCT a.customer_id), 1) AS retention_rate_pct
FROM customer_month_index a
LEFT JOIN customer_month_index b
ON b.customer_id = a.customer_id AND b.month_index = a.month_index + 1
GROUP BY a.month_index
ORDER BY a.month_index;| starting_month | active_customers | retained_next_month | retention_rate_pct |
|---|---|---|---|
| 1 | 1 | 1 | 100 |
| 2 | 2 | 0 | 0 |
| 3 | 2 | 1 | 50 |
| 4 | 3 | 1 | 33.3 |
| 5 | 1 | 0 | 0 |
| 6 | 3 | 2 | 66.7 |
| 7 | 2 | 1 | 50 |
| 8 | 1 | 1 | 100 |
| 9 | 3 | 2 | 66.7 |
| 10 | 2 | 0 | 0 |
Row 10 says 0% retention. That's the worst one on the whole table.
Look closer — month_index 10 is September 2023, and this dataset just doesn't have an October. There's no 'next month' to check yet, so every customer looks unretained by default. It's not that they churned — the data ends before we could find out.
Running…
Try it: Why not just add 1 to the calendar month string directly, like '2023-09' -> '2023-10'?
Run your query to see results here.
String math on '2023-12' would need to know December rolls over into a new year — special-case logic you’d have to write by hand, and easy to get wrong. Numbering the months you actually have with DENSE_RANK sidesteps all of that: month_index + 1 is just addition, and it’s always correct as long as your months don’t skip any gaps. (More on what happens when they do, later in this chapter.)
Your manager makes a fair point: the very last month in the dataset will always show 0% retention, because there’s no future month to check yet — that’s not a real signal, it’s just where the data runs out. Rebuild the retention table, excluding the most recent month from the starting cohort.
Hint 1
You need to know what the highest month_index in the dataset is before you can exclude it.
Hint 2
A scalar subquery — SELECT MAX(month_index) FROM months — gives you that highest index to compare against.
Hint 3
Add WHERE a.month_index < (SELECT MAX(month_index) FROM months) right before the GROUP BY.
Solution
Running…
Nine real months of retention, ranging from 0% to 100%, bouncing around a lot with no obvious direction — which, with one or two or three customers active in most months, is exactly what you’d expect. Small numbers make for noisy rates. The structural trick — number the periods, self-join on index + 1 — is the part that scales; run the same query against a table with a hundred thousand customers and the noise mostly washes out, leaving the real signal.
Month-over-month retention is a self-join on “same customer, next period” — number your periods with DENSE_RANK so “next period” is just +1, and always check whether your most recent period has had a fair chance to show retention yet.