Customers Whose Second Order Came Within a Week

Medium
165.

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. 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

ColumnType
order_idint
customer_idint
placed_atdatetime
order_valueint

Primary key: order_id.

One row per order. A customer never places two orders at the same instant.

Example

The rows must come back in the order shown.

Orders

order_idcustomer_idplaced_atorder_value
1612024-02-01 21:30:00320
2612024-02-08 09:00:00210
3622024-02-03 13:00:00450
4622024-02-11 13:00:00380
5632024-02-05 20:00:00260
6642024-02-10 23:55:00199
7642024-02-11 00:05:00240
8642024-02-12 12:00:00300
9652024-02-20 19:00:00280
10652024-02-14 19:00:00330

Output

customer_idfirst_order_onsecond_order_ondays_between
612024-02-012024-02-087
642024-02-102024-02-111
652024-02-142024-02-206
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
order_idint
customer_idint
placed_atdatetime
order_valueint

Primary key: order_id.

One row per order. A customer never places two orders at the same instant.

Examples

Example 1

Orders

order_idcustomer_idplaced_atorder_value
1612024-02-01 21:30:00320
2612024-02-08 09:00:00210
3622024-02-03 13:00:00450
4622024-02-11 13:00:00380
5632024-02-05 20:00:00260
6642024-02-10 23:55:00199
7642024-02-11 00:05:00240
8642024-02-12 12:00:00300
9652024-02-20 19:00:00280
10652024-02-14 19:00:00330

Output

customer_idfirst_order_onsecond_order_ondays_between
612024-02-012024-02-087
642024-02-102024-02-111
652024-02-142024-02-206

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_id

Another 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_id

Another 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 →