Customers Who Reordered Within 30 Days

Medium
113.

Customers Who Reordered Within 30 Days

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

ColumnType
order_idint
customer_idint
order_datedate
order_totalint

Primary key: order_id.

One row per order.

Example

The rows must come back in the order shown.

orders

order_idcustomer_idorder_dateorder_total
112024-02-01499
212024-03-021299
322024-02-03799
422024-03-05349
532024-02-102599
632024-02-10199
732024-02-11899
842024-02-151499
952024-03-01650
1052024-02-201100

Output

customer_idfirst_order_datesecond_order_datedays_between
12024-02-012024-03-0230
32024-02-102024-02-100
52024-02-202024-03-0110
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 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

ColumnType
order_idint
customer_idint
order_datedate
order_totalint

Primary key: order_id.

One row per order.

Examples

Example 1

orders

order_idcustomer_idorder_dateorder_total
112024-02-01499
212024-03-021299
322024-02-03799
422024-03-05349
532024-02-102599
632024-02-10199
732024-02-11899
842024-02-151499
952024-03-01650
1052024-02-201100

Output

customer_idfirst_order_datesecond_order_datedays_between
12024-02-012024-03-0230
32024-02-102024-02-100
52024-02-202024-03-0110

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_id

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