Customers Whose First Order Used a Coupon — SQL Problem
Growth wants to know which customers were acquired by a coupon.
- Difficulty: Medium
- Topics: Window Functions, Subqueries
- Dialect: MySQL
- Problem: #109
Problem statement
Growth wants to know which customers were acquired by a coupon. A customer's first order is the one with the earliest placed_at; if two of their orders share that timestamp, the one with the smaller order_id is first.
Return the customers whose first order carried a coupon (coupon_code not NULL), with the columns customer_id, order_id and coupon_code of that first order, ordered by customer_id. Coupons on later orders do not matter.
Tables
Table: orders
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| placed_at | datetime |
| coupon_code | varchar |
| order_total | int |
Primary key: order_id.
One row per order. coupon_code is NULL when no coupon was applied.
Examples
Example 1
orders
| order_id | customer_id | placed_at | coupon_code | order_total |
|---|---|---|---|---|
| 101 | 1 | 2024-03-01 10:00:00 | WELCOME100 | 899 |
| 102 | 1 | 2024-03-09 18:30:00 | NULL | 1499 |
| 103 | 2 | 2024-03-02 09:15:00 | NULL | 650 |
| 104 | 2 | 2024-03-05 20:00:00 | FESTIVE20 | 2100 |
| 105 | 3 | 2024-03-04 12:00:00 | NEWUSER | 349 |
| 106 | 3 | 2024-03-04 12:00:00 | NULL | 1200 |
| 107 | 4 | 2024-03-07 08:45:00 | NULL | 799 |
| 108 | 4 | 2024-03-07 08:45:00 | UPI50 | 999 |
Output
| customer_id | order_id | coupon_code |
|---|---|---|
| 1 | 101 | WELCOME100 |
| 3 | 105 | NEWUSER |
How to solve Customers Whose First Order Used a Coupon
The order of operations is the whole problem. If you keep only coupon orders and then take each customer's earliest, customer 2 — whose first order had no coupon, but whose second did — would wrongly appear. So pick the first order first, then test its coupon.
ROW_NUMBER() partitioned by customer and ordered by placed_at, order_id gives each customer's orders 1, 2, 3, …; the second sort key breaks the same-second tie the statement defines, so exactly one order gets 1. Filtering rn = 1 AND coupon_code IS NOT NULL in the outer query does the rest. Customer 4 shows why the tie-breaker matters: two orders at 08:45, and the smaller id, 107, had no coupon.
The correlated alternative says the same thing as a condition: an order is first if no other order of that customer comes before it in the (placed_at, order_id) order. It reads more like the definition but compares each order with all of the customer's others, while the window version is a single sort.
Reference solution (MySQL)
SELECT customer_id, order_id, coupon_code
FROM (
SELECT customer_id, order_id, coupon_code,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at, order_id) AS rn
FROM orders
) t
WHERE rn = 1 AND coupon_code IS NOT NULL
ORDER BY customer_idAnother way
SELECT o.customer_id, o.order_id, o.coupon_code
FROM orders o
WHERE o.coupon_code IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM orders e WHERE e.customer_id = o.customer_id
AND (e.placed_at < o.placed_at OR (e.placed_at = o.placed_at AND e.order_id < o.order_id)))
ORDER BY o.customer_id← Best-Rated Product in Each Category · Delivery SLA Breaches by Postal Zone →