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
Exercise 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.
Return
- Return customer_id, order_id, order_date, prev_order_date, days_since_prev in this exact left-to-right order.
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:
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.