Retention
Do new customers come back?.
Here's lesson one hundred, and a question every online business asks. Of the customers who bought for the first time in a year, how many came back within ninety days? Customers grouped by when they started are called a cohort.

- Lesson 100!
- Did new customers come back?
- Grouped into cohorts
A cohort report
Step one finds each customer's first order. Then we join back to all their orders. For each cohort year, we count customers, and count those with another order between one and ninety days after their first. Count distinct, with case inside, counts each returning customer only once. Run it. In twenty twenty four, one hundred and thirteen of two hundred and seventy one came back within ninety days. That's about forty two percent.
WITH firsts AS (
SELECT customer_id, MIN(order_date) AS first_order
FROM orders
GROUP BY customer_id
)
SELECT strftime('%Y', f.first_order) AS cohort,
COUNT(DISTINCT f.customer_id) AS customers,
COUNT(DISTINCT CASE
WHEN julianday(o.order_date) - julianday(f.first_order)
BETWEEN 1 AND 90
THEN f.customer_id END) AS back_in_90_days
FROM firsts AS f
JOIN orders AS o ON o.customer_id = f.customer_id
GROUP BY cohort;โโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโโ โ cohort โ customers โ back_in_90_days โ โโโโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโโค โ 2024 โ 271 โ 113 โ โ 2025 โ 192 โ 68 โ โโโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโ
What interviewers look for
Retention questions test everything at once. CTEs, joins, date maths, case, and count distinct. If you can explain this query step by step, you're ready for most analyst interviews.
Summary
A cohort report groups customers by when they started, and tracks whether they come back. Next, interview questions about ideas, not code.
Key takeaways- First order = cohort
- Back within 90 days
- COUNT(DISTINCT CASE ...)