Myntra: Shoppers Who Reordered the Size They Returned as Too Small
The fit-recommendation team wants to know whether its "you returned this size as too small" nudge would have helped: who sent back a size as too small and…
- Difficulty: Medium
- Topics: Joins
- Dialect: MySQL
- Problem: #807
Problem statement
The fit-recommendation team wants to know whether its "you returned this size as too small" nudge would have helped: who sent back a size as too small and then bought the same size again of the same brand and article type?
Find every item returned with reason = 'size_too_small' for which the same shopper later ordered an item of the same brand, article_type and size, with an ordered_on strictly after the returned item's ordered_on. Return the distinct combinations user_id, brand, article_type, size, 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: OrderItem
| Column | Type |
|---|---|
| order_item_id | int |
| user_id | int |
| brand | varchar |
| article_type | varchar |
| size | varchar |
| ordered_on | date |
Primary key: order_item_id.
One row per item ordered.
Table: ReturnRequest
| Column | Type |
|---|---|
| return_id | int |
| order_item_id | int |
| reason | varchar |
Primary key: return_id.
One row per returned item, with the reason the shopper gave.
Examples
Example 1
OrderItem
| order_item_id | user_id | brand | article_type | size | ordered_on |
|---|---|---|---|---|---|
| 1 | 1 | Roadster | Jeans | 32 | 2024-02-01 |
| 2 | 1 | Roadster | Jeans | 32 | 2024-03-10 |
| 3 | 1 | Roadster | Jeans | 32 | 2024-04-02 |
| 4 | 2 | HRX | T-shirt | M | 2024-02-05 |
| 5 | 2 | HRX | T-shirt | L | 2024-02-20 |
| 6 | 3 | Anouk | Kurta | S | 2024-03-01 |
| 7 | 3 | Anouk | Kurta | S | 2024-03-15 |
| 8 | 4 | Roadster | Jeans | 30 | 2024-05-05 |
| 9 | 4 | Roadster | Jeans | 30 | 2024-05-05 |
| 10 | 2 | Roadster | T-shirt | M | 2024-03-01 |
ReturnRequest
| return_id | order_item_id | reason |
|---|---|---|
| 1 | 1 | size_too_small |
| 2 | 4 | size_too_small |
| 3 | 6 | size_too_large |
| 4 | 8 | size_too_small |
Output
| user_id | brand | article_type | size |
|---|---|---|---|
| 1 | Roadster | Jeans | 32 |
How to solve Myntra: Shoppers Who Reordered the Size They Returned as Too Small
Two items of the same shopper have to be compared, so OrderItem appears twice. Alias a is the returned item: joining it to ReturnRequest with reason = 'size_too_small' keeps only items sent back as too small. Alias b is the reorder: a self join on the same user_id, brand, article_type and size, with b.ordered_on > a.ordered_on.
Each condition matters. Same-day items are not "later", so shopper 4's two identical jeans on one day do not count. A different size (shopper 2 going from M to L) is the nudge working, not failing. A return for being too large is a different story. And because one returned item can pair with several later orders, or one shopper can have several qualifying pairs, the raw join repeats combinations — DISTINCT collapses them to one row each.
The alternative filters the returned items with IN, tests for a later reorder with a correlated EXISTS, and uses GROUP BY to make the rows distinct. With an index on (user_id, brand, article_type, size) both find each item's partners directly.
Reference solution (MySQL)
SELECT DISTINCT a.user_id, a.brand, a.article_type, a.size
FROM OrderItem a
JOIN ReturnRequest r ON r.order_item_id = a.order_item_id AND r.reason = 'size_too_small'
JOIN OrderItem b ON b.user_id = a.user_id
AND b.brand = a.brand
AND b.article_type = a.article_type
AND b.size = a.size
AND b.ordered_on > a.ordered_onAnother way
SELECT a.user_id, a.brand, a.article_type, a.size
FROM OrderItem a
WHERE a.order_item_id IN (SELECT order_item_id FROM ReturnRequest WHERE reason = 'size_too_small')
AND EXISTS (SELECT 1 FROM OrderItem b
WHERE b.user_id = a.user_id AND b.brand = a.brand AND b.article_type = a.article_type
AND b.size = a.size AND b.ordered_on > a.ordered_on)
GROUP BY a.user_id, a.brand, a.article_type, a.size← Myntra: Size-Related Return Rate by Article Type and Size · Myntra: Creator Store Commission for October 2024 →