Myntra: Size-Related Return Rate by Article Type and Size
The size-chart team suspects that some sizes of some article types fit badly. They want the share of items in each size that came back because of size.
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #806
Problem statement
The size-chart team suspects that some sizes of some article types fit badly. They want the share of items in each size that came back because of size.
For every combination of article_type (from Style) and size with at least 3 items sold, return article_type, size, items (items sold), size_returns (items with a return request whose reason is size_too_small or size_too_large) and size_return_pct = 100 * size_returns / items, rounded to 1 decimal place. Returns for other reasons count as items but not as size 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: Style
| Column | Type |
|---|---|
| style_id | int |
| brand | varchar |
| article_type | varchar |
Primary key: style_id.
One row per style.
Table: OrderItem
| Column | Type |
|---|---|
| order_item_id | int |
| style_id | int |
| size | varchar |
Primary key: order_item_id.
One row per item sold, in one size.
Table: ReturnRequest
| Column | Type |
|---|---|
| return_id | int |
| order_item_id | int |
| reason | varchar |
Primary key: return_id.
At most one request per item.
Examples
Example 1
Style
| style_id | brand | article_type |
|---|---|---|
| 1 | Roadster | Jeans |
| 2 | HRX | T-shirt |
| 3 | Sangria | Jeans |
OrderItem
| order_item_id | style_id | size |
|---|---|---|
| 1 | 1 | M |
| 2 | 3 | M |
| 3 | 1 | M |
| 4 | 1 | L |
| 5 | 3 | L |
| 6 | 2 | M |
| 7 | 2 | M |
| 8 | 2 | M |
| 9 | 2 | M |
| 10 | 2 | S |
ReturnRequest
| return_id | order_item_id | reason |
|---|---|---|
| 1 | 1 | size_too_small |
| 2 | 2 | damaged |
| 3 | 4 | size_too_large |
| 4 | 6 | size_too_large |
| 5 | 7 | size_too_small |
| 6 | 8 | colour_different |
Output
| article_type | size | items | size_returns | size_return_pct |
|---|---|---|---|---|
| Jeans | M | 3 | 1 | 33.3 |
| T-shirt | M | 4 | 2 | 50 |
How to solve Myntra: Size-Related Return Rate by Article Type and Size
The rate has every item sold in the denominator and only the size-related returns in the numerator, so the items must all survive the join. The article type comes from Style by an inner join (every item has a style), and the return requests are attached with a LEFT JOIN: a kept item comes through with a NULL reason. Because each item has at most one request, the join never duplicates an item.
Grouping by article_type and size then gives COUNT(*) as items sold, and SUM(CASE WHEN reason IN ('size_too_small', 'size_too_large') THEN 1 ELSE 0 END) counts the size returns — a NULL reason or a damaged one adds 0. HAVING COUNT(*) >= 3 drops thin groups after aggregation, which WHERE cannot do. The percentage is 100 * size_returns / items with MySQL's exact division, rounded to one place.
The alternative marks each item with a 0/1 flag from a correlated EXISTS and aggregates the flags; it would stay correct even if an item could have several requests. Both are one pass over the items with key lookups.
Reference solution (MySQL)
SELECT s.article_type, oi.size,
COUNT(*) AS items,
SUM(CASE WHEN r.reason IN ('size_too_small', 'size_too_large') THEN 1 ELSE 0 END) AS size_returns,
ROUND(100 * SUM(CASE WHEN r.reason IN ('size_too_small', 'size_too_large') THEN 1 ELSE 0 END) / COUNT(*), 1) AS size_return_pct
FROM OrderItem oi
JOIN Style s ON s.style_id = oi.style_id
LEFT JOIN ReturnRequest r ON r.order_item_id = oi.order_item_id
GROUP BY s.article_type, oi.size
HAVING COUNT(*) >= 3Another way
SELECT article_type, size, COUNT(*) AS items, SUM(is_size) AS size_returns,
ROUND(100 * SUM(is_size) / COUNT(*), 1) AS size_return_pct
FROM (
SELECT s.article_type, oi.size,
CASE WHEN EXISTS (SELECT 1 FROM ReturnRequest r WHERE r.order_item_id = oi.order_item_id
AND r.reason IN ('size_too_small', 'size_too_large')) THEN 1 ELSE 0 END AS is_size
FROM OrderItem oi JOIN Style s ON s.style_id = oi.style_id
) t
GROUP BY article_type, size
HAVING COUNT(*) >= 3← Myntra: M-Now Deliveries Inside the 30-Minute Promise by Store · Myntra: Shoppers Who Reordered the Size They Returned as Too Small →