Layout

Days since the previous order

PROBLEM 17 / 24 · ADVANCEDAll problems ↗

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.

Outputcustomer_idorder_iddays_since_previous

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

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