Myntra: Serial Returners by Their Last Five Delivered Orders
To decide who loses free returns, the returns-abuse team looks at each shopper's recent behaviour only, not their whole history. Ignore cancelled orders.
- Difficulty: Hard
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #822
Problem statement
To decide who loses free returns, the returns-abuse team looks at each shopper's recent behaviour only, not their whole history.
Ignore cancelled orders. A shopper's last five orders are their five with the latest delivered_on, breaking ties by the higher order_id. Return user_id, name and returns_in_last_five for every shopper who has at least five non-cancelled orders and whose last five include at least four with status = 'returned' (exchanges are not returns). Return the rows in any order.
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: Shopper
| Column | Type |
|---|---|
| user_id | int |
| name | varchar |
Primary key: user_id.
One row per account.
Table: ShopOrder
| Column | Type |
|---|---|
| order_id | int |
| user_id | int |
| delivered_on | date |
| status | enum(kept, returned, exchanged, cancelled) |
Primary key: order_id.
One row per order. delivered_on is NULL exactly when the order was cancelled.
Examples
Example 1
Shopper
| user_id | name |
|---|---|
| 1 | Aarav |
| 2 | Diya |
| 3 | Kabir |
| 4 | Meera |
ShopOrder
| order_id | user_id | delivered_on | status |
|---|---|---|---|
| 1 | 1 | 2024-01-05 | kept |
| 2 | 1 | 2024-02-05 | returned |
| 3 | 1 | 2024-03-05 | returned |
| 4 | 1 | 2024-04-05 | kept |
| 5 | 1 | 2024-05-05 | returned |
| 6 | 1 | 2024-06-05 | returned |
| 7 | 2 | 2024-01-10 | returned |
| 8 | 2 | 2024-02-10 | returned |
| 9 | 2 | 2024-03-10 | exchanged |
| 10 | 2 | 2024-04-10 | returned |
| 11 | 2 | 2024-05-10 | returned |
| 12 | 2 | 2024-05-10 | kept |
| 13 | 3 | 2024-03-01 | returned |
| 14 | 3 | 2024-03-02 | returned |
| 15 | 3 | 2024-03-03 | returned |
| 16 | 3 | 2024-03-04 | returned |
| 17 | 4 | 2024-02-01 | returned |
| 18 | 4 | 2024-02-02 | returned |
| 19 | 4 | 2024-02-03 | kept |
| 20 | 4 | 2024-02-04 | returned |
| 21 | 4 | 2024-02-05 | returned |
| 22 | 4 | NULL | cancelled |
Output
| user_id | name | returns_in_last_five |
|---|---|---|
| 1 | Aarav | 4 |
| 4 | Meera | 4 |
How to solve Myntra: Serial Returners by Their Last Five Delivered Orders
This is a top-N per group problem followed by a condition on the group. Cancelled orders are removed first: they have no delivery date and were never in the shopper's hands. Then ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY delivered_on DESC, order_id DESC) numbers each shopper's orders from the most recent, and positions 1 to 5 are the last five. The order_id tie-break matters when two orders arrived the same day at the edge of the window.
Grouping those rows per shopper gives two numbers: how many orders were seen (fewer than five means the shopper does not have five orders yet, like Kabir) and how many of them were returned. Only returned counts — Diya's exchange is not a return, and her older return outside the window must not count either; looking at her whole history would wrongly flag her. Meera's cancelled order is ignored rather than pushing an older order out.
The alternative selects the last five without a window: an order is among them when fewer than five of the shopper's non-cancelled orders are newer in (date, id) order, counted by a correlated subquery, and HAVING applies both conditions. It is quadratic per shopper; the window version is one sort.
Reference solution (MySQL)
WITH ranked AS (
SELECT user_id, status,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY delivered_on DESC, order_id DESC) AS recency
FROM ShopOrder
WHERE status <> 'cancelled'
), last_five AS (
SELECT user_id,
COUNT(*) AS orders_seen,
SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returns_in_last_five
FROM ranked
WHERE recency <= 5
GROUP BY user_id
)
SELECT s.user_id, s.name, l.returns_in_last_five
FROM last_five l
JOIN Shopper s ON s.user_id = l.user_id
WHERE l.orders_seen = 5 AND l.returns_in_last_five >= 4Another way
SELECT s.user_id, s.name, SUM(CASE WHEN o.status = 'returned' THEN 1 ELSE 0 END) AS returns_in_last_five
FROM Shopper s
JOIN ShopOrder o ON o.user_id = s.user_id
WHERE o.status <> 'cancelled'
AND (SELECT COUNT(*) FROM ShopOrder p
WHERE p.user_id = o.user_id AND p.status <> 'cancelled'
AND (p.delivered_on > o.delivered_on OR (p.delivered_on = o.delivered_on AND p.order_id > o.order_id))) < 5
GROUP BY s.user_id, s.name
HAVING COUNT(*) = 5 AND SUM(CASE WHEN o.status = 'returned' THEN 1 ELSE 0 END) >= 4← Myntra: The Few Brands Making Up 80% of Each Category's Sale Revenue · Ola: Scooters Still Below MoveOS 4.2 →