Customers Who Reordered Within 30 Days — SQL Dates Problem
A healthy marketplace turns first-time buyers into repeat buyers quickly.
- Difficulty: Medium
- Topics: Dates, Window Functions
- Dialect: MySQL
- Problem: #113
Problem statement
A healthy marketplace turns first-time buyers into repeat buyers quickly. Order a customer's orders by order_date, then by order_id; the first two are their first and second order (two orders on the same day count as first and second, 0 days apart).
Return the customers whose second order came within 30 days of the first (30 days apart still counts), with the columns customer_id, first_order_date, second_order_date and days_between, ordered by customer_id. Customers with only one order are not listed.
Tables
Table: orders
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| order_date | date |
| order_total | int |
Primary key: order_id.
One row per order.
Examples
Example 1
orders
| order_id | customer_id | order_date | order_total |
|---|---|---|---|
| 1 | 1 | 2024-02-01 | 499 |
| 2 | 1 | 2024-03-02 | 1299 |
| 3 | 2 | 2024-02-03 | 799 |
| 4 | 2 | 2024-03-05 | 349 |
| 5 | 3 | 2024-02-10 | 2599 |
| 6 | 3 | 2024-02-10 | 199 |
| 7 | 3 | 2024-02-11 | 899 |
| 8 | 4 | 2024-02-15 | 1499 |
| 9 | 5 | 2024-03-01 | 650 |
| 10 | 5 | 2024-02-20 | 1100 |
Output
| customer_id | first_order_date | second_order_date | days_between |
|---|---|---|---|
| 1 | 2024-02-01 | 2024-03-02 | 30 |
| 3 | 2024-02-10 | 2024-02-10 | 0 |
| 5 | 2024-02-20 | 2024-03-01 | 10 |
How to solve Customers Who Reordered Within 30 Days
Number every customer's orders with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id). The tie-breaker makes the numbering unique even when a customer ordered twice on one day, as customer 3 did — those become orders 1 and 2, zero days apart, and the customer qualifies. Customer 5's rows are stored out of order, which the window's ORDER BY ignores.
Then pair row 1 with row 2. A self join on the numbered CTE (a.rn = 1, b.rn = 2, same customer) does it; an inner join automatically drops customers with a single order because they have no row 2. LEAD(order_date) read on row 1 gives the same pair in one pass, and there the single-order customers show up with a NULL second date that has to be filtered.
The window test is DATEDIFF(second, first) <= 30 or, equivalently, second <= DATE_ADD(first, INTERVAL 30 DAY). Customer 1 (30 days) is in, customer 2 (31 days) is out. One sort per customer, linear after that.
Reference solution (MySQL)
WITH numbered AS (
SELECT customer_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn
FROM orders
)
SELECT a.customer_id, a.order_date AS first_order_date, b.order_date AS second_order_date,
DATEDIFF(b.order_date, a.order_date) AS days_between
FROM numbered a
JOIN numbered b ON b.customer_id = a.customer_id AND b.rn = 2
WHERE a.rn = 1 AND DATEDIFF(b.order_date, a.order_date) <= 30
ORDER BY a.customer_idAnother way
SELECT customer_id, first_order_date, second_order_date, DATEDIFF(second_order_date, first_order_date) AS days_between
FROM (
SELECT customer_id, order_date AS first_order_date,
LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS second_order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn
FROM orders
) t
WHERE rn = 1 AND second_order_date IS NOT NULL AND second_order_date <= DATE_ADD(first_order_date, INTERVAL 30 DAY)
ORDER BY customer_id← Monthly GMV Against the Previous Calendar Month · GST Split Into CGST, SGST and IGST by Seller →