Retention: who came back within 30 days?
Of the customers who placed an order, what percentage placed another order within 30 days of their first one?
What it tests
Cohort-style thinking: anchor each user on their first event, look for a later event within a window, and compute a rate — the core of product and growth analytics interviews.
The pattern
Retention (first event, then a later event within N days). "Retention", "came back", "returned within", "repeat purchase rate", "day-7 / day-30".
How to approach it
- Find each customer's first order date (MIN per customer).
- For each customer, check whether any later order falls within 30 days of that first date.
- Turn that into 1 or 0 per customer (MAX of a CASE).
- Rate = 100.0 × customers who came back ÷ customers who ordered.
Edge cases to mention
- A second order on the same day as the first: does it count as "coming back"? The solution says no (
>); ask. - Customers whose first order is less than 30 days before the end of the data haven't had a full 30 days yet — exclude them from the denominator for a fair rate.
- Should cancelled orders count as activity? Filter by status if not.
Common mistakes
- Counting any second order, regardless of how long after.
- Dividing whole numbers and getting 0.
- Measuring from sign-up date when the question says first order (or the other way round).
Not true: A customer who places a second order counts as coming back, however long after the first.
In fact: Only a second order placed within 30 days of the first one counts. The query has to compare the two dates, not just check that a second order exists.
Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.