Flipkart: Sale-Acquired Buyers Who Came Back for the Next Sale
Marketing asks whether big sales bring in customers who stay.
- Difficulty: Hard
- Topics: Window Functions, Subqueries, Joins, Dates
- Dialect: MySQL
- Problem: #669
Problem statement
Marketing asks whether big sales bring in customers who stay. A customer is acquired by a sale event when their first non-cancelled order ever falls within that event's dates (inclusive). An acquired customer came back if they placed a non-cancelled order during the next sale event (the event with the next later starts_on).
For every sale event return event_name, acquired (customers it acquired, 0 if none) and returned_next (how many of them came back in the next event). For the latest event there is no next event yet, so returned_next is NULL. Order by the event's starts_on.
A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.
Tables
Table: SaleEvent
| Column | Type |
|---|---|
| event_id | int |
| event_name | varchar |
| starts_on | date |
| ends_on | date |
Primary key: event_id.
One row per sale event; events never overlap.
Table: CustomerOrder
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| ordered_on | date |
| order_status | enum(delivered, returned, cancelled) |
Primary key: order_id.
Every order a customer has ever placed, sale or not. Returned orders still count as orders; cancelled ones never do.
Examples
Example 1
SaleEvent
| event_id | event_name | starts_on | ends_on |
|---|---|---|---|
| 1 | Big Billion Days 2023 | 2023-10-08 | 2023-10-15 |
| 2 | Big Saving Days Jan 2024 | 2024-01-14 | 2024-01-19 |
| 3 | Big Saving Days Jul 2024 | 2024-07-20 | 2024-07-25 |
| 4 | Big Billion Days 2024 | 2024-09-27 | 2024-10-06 |
CustomerOrder
| order_id | customer_id | ordered_on | order_status |
|---|---|---|---|
| 1 | 101 | 2023-10-09 | delivered |
| 2 | 101 | 2024-01-15 | delivered |
| 3 | 102 | 2023-10-10 | cancelled |
| 4 | 102 | 2023-11-20 | delivered |
| 5 | 103 | 2023-10-12 | delivered |
| 6 | 103 | 2024-07-21 | delivered |
| 7 | 104 | 2024-01-16 | returned |
| 8 | 104 | 2024-07-22 | cancelled |
| 9 | 105 | 2024-07-20 | delivered |
| 10 | 105 | 2024-09-27 | delivered |
| 11 | 106 | 2024-09-30 | delivered |
Output
| event_name | acquired | returned_next |
|---|---|---|
| Big Billion Days 2023 | 2 | 1 |
| Big Saving Days Jan 2024 | 1 | 0 |
| Big Saving Days Jul 2024 | 1 | 1 |
| Big Billion Days 2024 | 1 | NULL |
How to solve Flipkart: Sale-Acquired Buyers Who Came Back for the Next Sale
The question chains three steps. Acquisition: each customer's first non-cancelled order, MIN(ordered_on) over their non-cancelled orders; a customer is acquired by the event whose dates contain that day. A customer whose first order was cancelled is judged by the next real order — which may fall outside every sale, in which case no event acquired them.
The next event: LEAD(event_id) OVER (ORDER BY starts_on) gives every event its successor in time, and NULL for the latest. Coming back: join each acquired customer to the next event's dates and to their non-cancelled orders inside it; DISTINCT makes a customer with three orders in the next sale count once.
The final SELECT starts from the events, so an event that acquired nobody still appears with acquired = 0 and returned_next = 0, while the latest event shows NULL because its next_id is NULL — the CASE keeps "not measurable yet" apart from "nobody came back". The alternative finds the next event with a correlated MIN(starts_on) > this start and counts returners with EXISTS inside SUM. Either is a handful of passes over orders and events.
Reference solution (MySQL)
WITH firsts AS (
SELECT customer_id, MIN(ordered_on) AS first_on
FROM CustomerOrder WHERE order_status <> 'cancelled' GROUP BY customer_id
), ev AS (
SELECT event_id, event_name, starts_on, ends_on, LEAD(event_id) OVER (ORDER BY starts_on) AS next_id
FROM SaleEvent
), acq AS (
SELECT e.event_id, e.next_id, f.customer_id
FROM ev e JOIN firsts f ON f.first_on BETWEEN e.starts_on AND e.ends_on
), came_back AS (
SELECT DISTINCT a.event_id, a.customer_id
FROM acq a
JOIN SaleEvent n ON n.event_id = a.next_id
JOIN CustomerOrder o ON o.customer_id = a.customer_id AND o.order_status <> 'cancelled'
AND o.ordered_on BETWEEN n.starts_on AND n.ends_on
)
SELECT e.event_name,
(SELECT COUNT(*) FROM acq a WHERE a.event_id = e.event_id) AS acquired,
CASE WHEN e.next_id IS NULL THEN NULL
ELSE (SELECT COUNT(*) FROM came_back c WHERE c.event_id = e.event_id) END AS returned_next
FROM ev e
ORDER BY e.starts_onAnother way
WITH f AS (
SELECT customer_id, MIN(ordered_on) AS first_on FROM CustomerOrder WHERE order_status <> 'cancelled' GROUP BY customer_id
), a AS (
SELECT s.event_id, f.customer_id FROM SaleEvent s JOIN f ON f.first_on >= s.starts_on AND f.first_on <= s.ends_on
)
SELECT s.event_name, COUNT(a.customer_id) AS acquired,
CASE WHEN NOT EXISTS (SELECT 1 FROM SaleEvent z WHERE z.starts_on > s.starts_on) THEN NULL
ELSE SUM(CASE WHEN EXISTS (
SELECT 1 FROM CustomerOrder o JOIN SaleEvent nx ON o.ordered_on BETWEEN nx.starts_on AND nx.ends_on
WHERE o.customer_id = a.customer_id AND o.order_status <> 'cancelled'
AND nx.starts_on = (SELECT MIN(z.starts_on) FROM SaleEvent z WHERE z.starts_on > s.starts_on))
THEN 1 ELSE 0 END) END AS returned_next
FROM SaleEvent s LEFT JOIN a ON a.event_id = s.event_id
GROUP BY s.event_id, s.event_name, s.starts_on
ORDER BY s.starts_on← Flipkart: Month-to-Month Sales Quartile Movement Matrix · Flipkart: Unscanned Ekart AWB Numbers as Ranges per Hub →