Customers Whose Second Order Came Within a Week
A customer who orders again within a week of their first order is far more likely to stay.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #165
Problem statement
A customer who orders again within a week of their first order is far more likely to stay. For each customer, the first order is their earliest by placed_at and the second is the next one after it; the gap is DATEDIFF between the two dates (time of day ignored).
Return customer_id, first_order_on, second_order_on (both as dates, YYYY-MM-DD) and days_between for every customer whose second order came within 7 days (0 to 7 inclusive). Customers with only one order are left out. Order the rows by customer_id.
Tables
Table: Orders
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| placed_at | datetime |
| order_value | int |
Primary key: order_id.
One row per order. A customer never places two orders at the same instant.
Examples
Example 1
Orders
| order_id | customer_id | placed_at | order_value |
|---|---|---|---|
| 1 | 61 | 2024-02-01 21:30:00 | 320 |
| 2 | 61 | 2024-02-08 09:00:00 | 210 |
| 3 | 62 | 2024-02-03 13:00:00 | 450 |
| 4 | 62 | 2024-02-11 13:00:00 | 380 |
| 5 | 63 | 2024-02-05 20:00:00 | 260 |
| 6 | 64 | 2024-02-10 23:55:00 | 199 |
| 7 | 64 | 2024-02-11 00:05:00 | 240 |
| 8 | 64 | 2024-02-12 12:00:00 | 300 |
| 9 | 65 | 2024-02-20 19:00:00 | 280 |
| 10 | 65 | 2024-02-14 19:00:00 | 330 |
Output
| customer_id | first_order_on | second_order_on | days_between |
|---|---|---|---|
| 61 | 2024-02-01 | 2024-02-08 | 7 |
| 64 | 2024-02-10 | 2024-02-11 | 1 |
| 65 | 2024-02-14 | 2024-02-20 | 6 |
How to solve Customers Whose Second Order Came Within a Week
The first and second orders are positions in each customer's own timeline, which is what ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at) assigns: 1 for the earliest, 2 for the next. Joining the rank-1 row to the rank-2 row of the same customer puts both timestamps side by side; an inner join drops customers with a single order. LEAD(placed_at) read on the rank-1 row gives the same second timestamp without a join.
Order ids are not a timeline — customer 65's order 10 came before order 9 — so the ordering must be on placed_at. The gap is in calendar days: DATEDIFF ignores the time of day, so an order at 23:55 and one at 00:05 the next day are 1 day apart, and 7 days counts as within a week.
Without windows: the first order is MIN(placed_at) per customer, and the second is the minimum of the later ones — a grouped self join. That relies on a customer never ordering twice at one instant, which the table promises. The window plan is one sort per customer.
Reference solution (MySQL)
WITH ranked AS (
SELECT customer_id, placed_at,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at) AS rn
FROM Orders
)
SELECT f.customer_id,
CAST(f.placed_at AS DATE) AS first_order_on,
CAST(s.placed_at AS DATE) AS second_order_on,
DATEDIFF(s.placed_at, f.placed_at) AS days_between
FROM ranked f
JOIN ranked s ON s.customer_id = f.customer_id AND s.rn = 2
WHERE f.rn = 1 AND DATEDIFF(s.placed_at, f.placed_at) <= 7
ORDER BY f.customer_idAnother way
SELECT customer_id, DATE(placed_at) AS first_order_on, DATE(next_at) AS second_order_on, DATEDIFF(next_at, placed_at) AS days_between
FROM (SELECT customer_id, placed_at,
LEAD(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at) AS next_at,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at) AS rn
FROM Orders) t
WHERE rn = 1 AND next_at IS NOT NULL AND DATEDIFF(next_at, placed_at) BETWEEN 0 AND 7
ORDER BY customer_idAnother way
SELECT a.customer_id, CAST(a.first_at AS DATE) AS first_order_on, CAST(MIN(o.placed_at) AS DATE) AS second_order_on,
DATEDIFF(MIN(o.placed_at), a.first_at) AS days_between
FROM (SELECT customer_id, MIN(placed_at) AS first_at FROM Orders GROUP BY customer_id) a
JOIN Orders o ON o.customer_id = a.customer_id AND o.placed_at > a.first_at
GROUP BY a.customer_id, a.first_at
HAVING DATEDIFF(MIN(o.placed_at), a.first_at) <= 7
ORDER BY a.customer_id← Restaurants Cooking Faster Than Their City Average · Longest On-Time Delivery Streak of Each Rider →