Time to First Repeat Purchase
Return each repeat customer’s first two order dates and the days between them.
- Window functions
- Subqueries
- Aggregation
- CASE expressions
- Date analysis
Exercise brief
Understand the request
Retention product manager Onboarding analysis needs the time from first purchase to first repeat purchase.
Onboarding analysis needs the time from first purchase to first repeat purchase. Return each repeat customer’s first two order dates and the days between them.
Return
- Return customer_id, first_order, second_order, days_to_repeat in this exact left-to-right order.
Constraints
- Rank orders per customer with order_id as a tie-breaker.
- Use only ranks one and two to calculate the metric.
- Exclude customers without a second purchase.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
orders
order_idINTEGERcustomer_idINTEGERorder_dateDATE
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Number each customer's orders with ROW_NUMBER() PARTITION BY customer_id ORDER BY date.
Hint 2
Pivot rows 1 and 2 into columns with MIN(CASE WHEN rn = 1/2 …).
Hint 3
days_to_repeat = day-difference between the second and first order.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH ranked AS (SELECT customer_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn FROM orders) SELECT customer_id, MIN(CASE WHEN rn = 1 THEN order_date END) AS first_order, MIN(CASE WHEN rn = 2 THEN order_date END) AS second_order, CAST(julianday(MIN(CASE WHEN rn = 2 THEN order_date END)) - julianday(MIN(CASE WHEN rn = 1 THEN order_date END)) AS INTEGER) AS days_to_repeat FROM ranked WHERE rn <= 2 GROUP BY customer_id ORDER BY days_to_repeat, customer_id;Why this works
'Time to second purchase' is a leading indicator of customer stickiness. Number orders per customer, isolate the 1st and 2nd via conditional aggregation, then diff their dates. Customers with only one order would yield a NULL second_order. The window + day-difference idiom ports cleanly across engines.
Success check
Each repeat customer appears once and the gap uses only their first and second orders.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| customer_id | first_order | second_order | days_to_repeat |
|---|---|---|---|
| 1 | 2024-01-15 | 2024-02-10 | 26 |
| 4 | 2024-03-15 | 2024-06-01 | 78 |
| 5 | 2024-04-10 | 2024-07-10 | 91 |
| 2 | 2023-02-15 | 2024-01-20 | 339 |
| 3 | 2023-01-10 | 2024-02-25 | 411 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
Open the interactive workspace and practice across SQL topics.