A second purchase within seven days
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.
Output
customer_iddays_to_secondquery.sql
DuckDB SQLRestoring your saved draft…
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_idstatuscompleted / cancelled / pending
Fictional teaching data. Money is USD cents; timestamps are UTC.