Customers Whose First Order Used a Coupon

Medium
109.

Customers Whose First Order Used a Coupon

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

ColumnType
order_idint
customer_idint
placed_atdatetime
coupon_codevarchar
order_totalint

Primary key: order_id.

One row per order. coupon_code is NULL when no coupon was applied.

Example

The rows must come back in the order shown.

orders

order_idcustomer_idplaced_atcoupon_codeorder_total
10112024-03-01 10:00:00WELCOME100899
10212024-03-09 18:30:00NULL1499
10322024-03-02 09:15:00NULL650
10422024-03-05 20:00:00FESTIVE202100
10532024-03-04 12:00:00NEWUSER349
10632024-03-04 12:00:00NULL1200
10742024-03-07 08:45:00NULL799
10842024-03-07 08:45:00UPI50999

Output

customer_idorder_idcoupon_code
1101WELCOME100
3105NEWUSER
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 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

ColumnType
order_idint
customer_idint
placed_atdatetime
coupon_codevarchar
order_totalint

Primary key: order_id.

One row per order. coupon_code is NULL when no coupon was applied.

Examples

Example 1

orders

order_idcustomer_idplaced_atcoupon_codeorder_total
10112024-03-01 10:00:00WELCOME100899
10212024-03-09 18:30:00NULL1499
10322024-03-02 09:15:00NULL650
10422024-03-05 20:00:00FESTIVE202100
10532024-03-04 12:00:00NEWUSER349
10632024-03-04 12:00:00NULL1200
10742024-03-07 08:45:00NULL799
10842024-03-07 08:45:00UPI50999

Output

customer_idorder_idcoupon_code
1101WELCOME100
3105NEWUSER

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_id

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