Myntra: EORS First-Time Buyers Who Ordered Again Within 90 Days
Big sale events bring in many first-time shoppers. The growth team wants to know, per sale edition, how many shoppers made their very first purchase during…
- Difficulty: Hard
- Topics: Subqueries, Dates, Aggregation
- Dialect: MySQL
- Problem: #818
Problem statement
Big sale events bring in many first-time shoppers. The growth team wants to know, per sale edition, how many shoppers made their very first purchase during it and how many of them came back soon after.
A shopper's first order is their earliest order with a status other than cancelled. They are a new buyer of an edition when that first order's placed_on falls between the edition's starts_on and ends_on, inclusive. A new buyer came back when they have another non-cancelled order placed on a later day, at most 90 days after the first order's date. For every edition return edition_name, new_buyers and came_back, ordered by starts_on.
A practice scenario set at Myntra: the tables and data are invented, and this is not a question from, or endorsed by, Myntra.
Tables
Table: SaleEdition
| Column | Type |
|---|---|
| edition_id | int |
| edition_name | varchar |
| starts_on | date |
| ends_on | date |
Primary key: edition_id.
One row per sale event; editions never overlap.
Table: ShopOrder
| Column | Type |
|---|---|
| order_id | int |
| user_id | int |
| placed_on | date |
| status | enum(delivered, returned, cancelled) |
Primary key: order_id.
One row per order.
Examples
Example 1
SaleEdition
| edition_id | edition_name | starts_on | ends_on |
|---|---|---|---|
| 1 | EORS Summer 2024 | 2024-06-01 | 2024-06-06 |
| 2 | Big Fashion Festival 2024 | 2024-10-01 | 2024-10-08 |
| 3 | EORS Winter 2024 | 2024-12-14 | 2024-12-19 |
ShopOrder
| order_id | user_id | placed_on | status |
|---|---|---|---|
| 1 | 1 | 2024-06-01 | delivered |
| 2 | 1 | 2024-08-30 | delivered |
| 3 | 2 | 2024-05-20 | cancelled |
| 4 | 2 | 2024-06-06 | delivered |
| 5 | 2 | 2024-06-06 | returned |
| 6 | 2 | 2024-09-05 | delivered |
| 7 | 3 | 2024-05-30 | delivered |
| 8 | 3 | 2024-06-02 | delivered |
| 9 | 4 | 2024-10-08 | returned |
| 10 | 4 | 2024-10-09 | cancelled |
| 11 | 4 | 2024-11-01 | delivered |
Output
| edition_name | new_buyers | came_back |
|---|---|---|
| EORS Summer 2024 | 2 | 1 |
| Big Fashion Festival 2024 | 1 | 1 |
| EORS Winter 2024 | 0 | 0 |
How to solve Myntra: EORS First-Time Buyers Who Ordered Again Within 90 Days
The question has three layers. First, which orders count: cancelled ones never do, so they are filtered out before anything else. Shopper 2's cancelled order on 20 May would otherwise make 20 May their first order and hide them from the summer sale.
Second, each shopper's first order: MIN(placed_on) per shopper over the valid orders. A shopper is a new buyer of the edition whose window contains that date; joining firsts to SaleEdition with BETWEEN starts_on AND ends_on (inclusive) assigns each shopper to at most one edition, since editions do not overlap. Shopper 3 first bought on 30 May, before the sale, and is nobody's new buyer. The join is a LEFT JOIN from the editions so that the winter edition, with no new buyers, still gets its row with zeros.
Third, the come-back test: another valid order on a later day (placed_on > first_on, so shopper 2's second order on the same day does not count) no more than 90 days on (DATEDIFF <= 90; 90 counts, 91 does not). COUNT(CASE WHEN EXISTS (…) THEN 1 END) counts the shoppers who pass; for an edition with no new buyers there is nothing to count. The alternative uses a window MIN for the first date and finds each shopper's next later order date with a conditional MIN, then tests the gap — one pass instead of a subquery per shopper.
Reference solution (MySQL)
WITH valid_orders AS (
SELECT user_id, placed_on FROM ShopOrder WHERE status <> 'cancelled'
), firsts AS (
SELECT user_id, MIN(placed_on) AS first_on FROM valid_orders GROUP BY user_id
)
SELECT e.edition_name,
COUNT(f.user_id) AS new_buyers,
COUNT(CASE WHEN EXISTS (SELECT 1 FROM valid_orders v
WHERE v.user_id = f.user_id AND v.placed_on > f.first_on
AND DATEDIFF(v.placed_on, f.first_on) <= 90)
THEN 1 END) AS came_back
FROM SaleEdition e
LEFT JOIN firsts f ON f.first_on BETWEEN e.starts_on AND e.ends_on
GROUP BY e.edition_id, e.edition_name, e.starts_on
ORDER BY e.starts_onAnother way
WITH valid_orders AS (
SELECT user_id, placed_on FROM ShopOrder WHERE status IN ('delivered', 'returned')
), ranked AS (
SELECT user_id, placed_on, MIN(placed_on) OVER (PARTITION BY user_id) AS first_on FROM valid_orders
), seconds AS (
SELECT user_id, first_on, MIN(CASE WHEN placed_on > first_on THEN placed_on END) AS next_on
FROM ranked GROUP BY user_id, first_on
)
SELECT e.edition_name,
COUNT(s.user_id) AS new_buyers,
SUM(CASE WHEN s.next_on IS NOT NULL AND DATEDIFF(s.next_on, s.first_on) <= 90 THEN 1 ELSE 0 END) AS came_back
FROM SaleEdition e
LEFT JOIN seconds s ON s.first_on >= e.starts_on AND s.first_on <= e.ends_on
GROUP BY e.edition_id, e.edition_name, e.starts_on
ORDER BY e.starts_on← Myntra: The Best Price Box for Each Style Across Three Offers · Myntra: Each Brand's Three Most-Wishlisted Styles in One Row →