Layout

Days until the next purchase

PROBLEM 18 / 24 · ADVANCEDAll problems ↗

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.

Outputcustomer_idorder_iddays_until_next

query.sql

DuckDB SQL

Restoring your saved draft…

⌘ / Ctrl + Enter to run

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_id
  • status completed / cancelled / pending

Fictional teaching data. Money is USD cents; timestamps are UTC.