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

  1. Find each customer's first order date (MIN per customer).
  2. For each customer, check whether any later order falls within 30 days of that first date.
  3. Turn that into 1 or 0 per customer (MAX of a CASE).
  4. Rate = 100.0 × customers who came back ÷ customers who ordered.

Edge cases to mention

Common mistakes

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.