Layout

A second purchase within seven days

PROBLEM 21 / 24 · ADVANCEDAll problems ↗

Find buyers whose second completed order is between 0 and 7 calendar days (inclusive) after their first. Rank by ordered_at then order_id. Return customer_id and days_to_second. A same-date second order counts; cancelled/pending orders do not. Row order does not matter.

Outputcustomer_iddays_to_second

query.sql

DuckDB SQL

Restoring your saved draft…

⌘ / Ctrl + Enter to run

Run a query to see results.

Reveal solution & explanation
WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id) AS rn FROM orders WHERE status = 'completed'), dates AS (SELECT customer_id, MAX(CASE WHEN rn = 1 THEN CAST(ordered_at AS DATE) END) AS first_date, MAX(CASE WHEN rn = 2 THEN CAST(ordered_at AS DATE) END) AS second_date FROM ranked GROUP BY customer_id), gaps AS (SELECT customer_id, date_diff('day', first_date, second_date) AS days_to_second FROM dates) SELECT * FROM gaps WHERE days_to_second BETWEEN 0 AND 7;

Select the first two actual purchases before evaluating the gap. Do not find any convenient close pair later in the buyer's history.

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.