Retention: who came back within 30 days?

Step 1 Solve it yourself first

Tables you will use

orders

What it really 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".

Picture it

flowchart LR
  F["first order per customer"] --> P["Priya: next order after 1 day"]
  F --> T["Tom: after 40 days"]
  F --> L["Lena: after 8 days"]
  F --> Z["Zoe: no second order"]
  P --> R["came back within 30 days:<br/>Priya, Lena<br/>2 of 4 = 50%"]
  L --> R

The approach, step by step

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.

WITH firsts AS (
  SELECT customer_id, MIN(order_date) AS first_date
  FROM orders
  GROUP BY customer_id
),
flags AS (
  SELECT f.customer_id,
         MAX(CASE WHEN o.order_date > f.first_date
                   AND julianday(o.order_date) - julianday(f.first_date) <= 30
                  THEN 1 ELSE 0 END) AS came_back
  FROM firsts f
  JOIN orders o ON o.customer_id = f.customer_id
  GROUP BY f.customer_id
)
SELECT COUNT(*) AS customers,
       SUM(came_back) AS came_back_within_30_days,
       ROUND(100.0 * SUM(came_back) / COUNT(*), 1) AS retention_pct
FROM flags;

WITH numbered AS (
  SELECT customer_id, order_date,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn,
         LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_date
  FROM orders
)
SELECT c.name, n.order_date AS first_order, n.next_date AS second_order,
       julianday(n.next_date) - julianday(n.order_date) AS gap_days
FROM numbered n
JOIN customers c ON c.customer_id = n.customer_id
WHERE n.rn = 1
ORDER BY c.customer_id;

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  Retention and cohort analysis in SQL (video)

  YouTube search, free

• Practice

  DataLemur — retention questions

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why each customer is reduced to a single 1 or 0 before the rate is calculated.

   Show answer

   Because the question is about customers, not orders: "what share of customers came back?". Every customer must count exactly once.

   If you worked on order rows instead, Priya, with 5 orders, would count five times and her habits would swamp everyone else's. So each customer is first reduced to one row with a single flag: 1 if they came back within 30 days, 0 if not (MAX over their orders turns "any qualifying order" into one 1). Then the rate is simple: the sum of the flags (customers who came back) ÷ the count of rows (customers).

2. Writefrom a blank page, then run it

   Compute the share of customers who came back within 7 days.

   Show answer

   Change 30 to 7. Only Priya came back within a week (her second order was the next day). Lena's came 8 days later and Tom's 40 days later, and Zoe has only one order. 1 out of 4 is 25.0%.

   sqlCopy

   WITH firsts AS (
     SELECT customer_id, MIN(order_date) AS first_date
     FROM orders
     GROUP BY customer_id
   ),
   flags AS (
     SELECT f.customer_id,
            MAX(CASE WHEN o.order_date > f.first_date
                      AND julianday(o.order_date) - julianday(f.first_date) <= 7
                     THEN 1 ELSE 0 END) AS came_back
     FROM firsts f
     JOIN orders o ON o.customer_id = f.customer_id
     GROUP BY f.customer_id
   )
   SELECT COUNT(*) AS customers,
          SUM(came_back) AS came_back_within_7_days,
          ROUND(100.0 * SUM(came_back) / COUNT(*), 1) AS retention_pct
   FROM flags;

   Try it

   1 row

   customers	came_back_within_7_days	retention_pct
   4	1	25

3. Predictsay it before you run it

   What would retention_pct be with a 45-day window?

   Show answer

   75.0. Tom now counts as well: his second order (1 March) came 40 days after his first (21 January), inside 45 days but outside 30. With Priya and Lena, that is 3 of the 4 customers. Zoe still has only one order.

   sqlCopy

   WITH firsts AS (
     SELECT customer_id, MIN(order_date) AS first_date
     FROM orders
     GROUP BY customer_id
   ),
   flags AS (
     SELECT f.customer_id,
            MAX(CASE WHEN o.order_date > f.first_date
                      AND julianday(o.order_date) - julianday(f.first_date) <= 45
                     THEN 1 ELSE 0 END) AS came_back
     FROM firsts f
     JOIN orders o ON o.customer_id = f.customer_id
     GROUP BY f.customer_id
   )
   SELECT COUNT(*) AS customers,
          SUM(came_back) AS came_back_within_45_days,
          ROUND(100.0 * SUM(came_back) / COUNT(*), 1) AS retention_pct
   FROM flags;

   Try it

   1 row

   customers	came_back_within_45_days	retention_pct
   4	3	75

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Change the solution so only delivered orders count as activity. Who changes?

   Show answer

   Filter to delivered orders once, in a first CTE, and use it in both later steps, so the first order and the return visit are both delivered ones.

   Who changes:
   • Lena: her first order (106) was cancelled, so her first delivered order is 107, and she has none after it. She no longer came back.
   • Zoe: her only order is pending, so she has no delivered orders and drops out of the customers counted entirely.
   • Priya (came back) and Tom (didn't) are unchanged.

   The rate becomes 1 of 3: 33.3%. The query below shows the flags per customer, to make the change visible.

   sqlCopy

   WITH delivered AS (
     SELECT customer_id, order_date
     FROM orders
     WHERE status = 'delivered'
   ),
   firsts AS (
     SELECT customer_id, MIN(order_date) AS first_date
     FROM delivered
     GROUP BY customer_id
   ),
   flags AS (
     SELECT f.customer_id,
            MAX(CASE WHEN d.order_date > f.first_date
                      AND julianday(d.order_date) - julianday(f.first_date) <= 30
                     THEN 1 ELSE 0 END) AS came_back
     FROM firsts f
     JOIN delivered d ON d.customer_id = f.customer_id
     GROUP BY f.customer_id
   )
   SELECT c.name, fl.came_back
   FROM flags fl
   JOIN customers c ON c.customer_id = fl.customer_id
   ORDER BY c.customer_id;

   Try it

   3 rows

   name	came_back
   Priya	1
   Tom	0
   Lena	0

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

I anchor each customer on their first order with MIN per customer, join back to their orders, and flag whether any later order falls within 30 days, taking MAX of a CASE so each customer is one or zero. The retention rate is the sum of flags over the number of customers, times 100.0. I'd raise two definitional points: whether a same-day second order counts, and excluding customers who haven't had a full 30 days yet.

Follow-up questions

Tick each check when you can do it without notes