Days since the previous order
For every completed order, return customer_id, order_id, and days_since_previous. Within each customer, order by ordered_at then order_id. Use whole calendar-date differences with date_diff('day', previous date, current date). The first order must have NULL, not zero. Row order does not matter.
Output
customer_idorder_iddays_since_previousquery.sql
DuckDB SQLRestoring your saved draft…
Run a query to see results.
Reveal solution & explanation
WITH history AS (SELECT *, LAG(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id) AS previous_at FROM orders WHERE status = 'completed') SELECT customer_id, order_id, date_diff('day', CAST(previous_at AS DATE), CAST(ordered_at AS DATE)) AS days_since_previous FROM history;LAG is within customer history. NULL on the first order means no previous event; zero can represent two actual orders on the same date.
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.