Days Between Consecutive Orders per Customer
Return every order with the previous order date and day gap within that customer.
- Window functions
- Date analysis
- Type conversion
- Sorting
Interview brief
Understand the request
Lifecycle analytics engineer Reorder cadence analysis needs the elapsed days between each customer’s consecutive purchases.
Reorder cadence analysis needs the elapsed days between each customer’s consecutive purchases. Return every order with the previous order date and day gap within that customer.
Constraints
- Use LAG partitioned by customer_id.
- Order the window by order_date and order_id.
- Keep the first order with NULL previous date and gap.
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
PARTITION BY customer_id so LAG only looks within one customer's history.
Hint 2
LAG(order_date) returns the previous row’s date; the first order per customer is NULL.
Hint 3
days_since_prev = day-difference between this order and the LAG value.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT customer_id, order_id, order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS prev_order_date, CAST(julianday(order_date) - julianday(LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date, order_id)) AS INTEGER) AS days_since_prev FROM orders ORDER BY customer_id, order_date, order_id;Why this works
Inter-purchase gap (recency cadence) drives reorder and retention models. The pattern is LAG partitioned by customer and ordered by date, then a day-difference between the current and previous date. The first order in each partition has no predecessor, so its gap is NULL. Only the day-difference syntax changes per engine.
Success check
Every order remains at order grain and gaps never cross customer boundaries.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| customer_id | order_id | order_date | prev_order_date | days_since_prev |
|---|---|---|---|---|
| 1 | 1 | 2024-01-15 | NULL | NULL |
| 1 | 3 | 2024-02-10 | 2024-01-15 | 26 |
| 1 | 7 | 2024-04-01 | 2024-02-10 | 51 |
| 1 | 12 | 2024-06-15 | 2024-04-01 | 75 |
| 1 | 17 | 2024-11-20 | 2024-06-15 | 158 |
| 2 | 20 | 2023-02-15 | NULL | NULL |
| 2 | 2 | 2024-01-20 | 2023-02-15 | 339 |
| 2 | 5 | 2024-03-05 | 2024-01-20 | 45 |
| 2 | 10 | 2024-05-20 | 2024-03-05 | 76 |
| 2 | 15 | 2024-09-01 | 2024-05-20 | 104 |
Previewing 10 of 20 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics: