Days until the next purchase
For every completed order, return customer_id, order_id, and days_until_next using the next completed order for that customer. Order by ordered_at then order_id; use calendar-date differences. The last order must have NULL. Row order does not matter.
Output
customer_idorder_iddays_until_nextquery.sql
DuckDB SQLRestoring your saved draft…
Run a query to see results.
Reveal solution & explanation
WITH history AS (SELECT *, LEAD(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id) AS next_at FROM orders WHERE status = 'completed') SELECT customer_id, order_id, date_diff('day', CAST(ordered_at AS DATE), CAST(next_at AS DATE)) AS days_until_next FROM history;A missing next purchase is censored by the dataset horizon. It is not a zero-day repeat or proof that the buyer never returns.
Data & schema
- order_id
- INTEGER · PK
- customer_id
- INTEGER
- ordered_at
- TIMESTAMP
- status
- VARCHAR
- order_total_cents
- INTEGER
Table notes & relationships
One row per order. Only completed orders count as revenue unless a question says otherwise. Times are UTC.
customer_id→ customers.customer_idstatuscompleted / cancelled / pending
Fictional teaching data. Money is USD cents; timestamps are UTC.