Myntra: Sizes Over-Represented in Size Returns per Article Type
A size whose fit is off shows up in size-related returns more often than its share of sales would predict.
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #820
Problem statement
A size whose fit is off shows up in size-related returns more often than its share of sales would predict. The size-chart team wants those sizes, article type by article type.
Within each article_type, a size's sales share is its items divided by all the article type's items, and its return share is its size_return items divided by all the article type's size_return items. Return article_type, size, items and size_returns for every size whose return share is strictly greater than its sales share. Article types with no size returns at all are skipped. 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: OrderItem
| Column | Type |
|---|---|
| order_item_id | int |
| article_type | varchar |
| size | varchar |
| outcome | enum(kept, size_return, other_return) |
Primary key: order_item_id.
One row per item sold. size_return means it came back because it did not fit; other_return covers every other reason.
Examples
Example 1
OrderItem
| order_item_id | article_type | size | outcome |
|---|---|---|---|
| 1 | Jeans | 30 | kept |
| 2 | Jeans | 30 | kept |
| 3 | Jeans | 30 | size_return |
| 4 | Jeans | 30 | kept |
| 5 | Jeans | 32 | size_return |
| 6 | Jeans | 32 | size_return |
| 7 | Jeans | 32 | size_return |
| 8 | Jeans | 32 | kept |
| 9 | Jeans | 34 | kept |
| 10 | Jeans | 34 | other_return |
| 11 | Kurta | M | size_return |
| 12 | Kurta | M | other_return |
| 13 | Kurta | M | kept |
| 14 | Kurta | L | size_return |
| 15 | Sneakers | 8 | other_return |
| 16 | Sneakers | 8 | other_return |
Output
| article_type | size | items | size_returns |
|---|---|---|---|
| Jeans | 32 | 4 | 3 |
| Kurta | L | 1 | 1 |
How to solve Myntra: Sizes Over-Represented in Size Returns per Article Type
Each size has to be compared with its whole article type, so the query needs per-size counts and per-article totals side by side. The first CTE groups by (article_type, size): COUNT(*) items and a conditional SUM of size returns. The second puts the article type's totals on each of those rows with window sums — SUM(items) OVER (PARTITION BY article_type) and the same for returns — which is a window over an aggregate, evaluated after the grouping.
The test "return share > sales share" is size_returns / article_returns > items / article_items. Multiplying both sides by the two positive totals gives size_returns * article_items > items * article_returns, all integers, so there is no rounding at all and an exact tie (a size returning exactly its share) is correctly excluded. The division is undefined when an article type has no size returns, so those are filtered out first; Sneakers, with only other returns, never appears. Jeans size 30 sells 40% and takes 25% of size returns — under-represented — while size 32 sells 40% and takes 75%.
The alternative joins the per-size aggregate to a per-article aggregate and compares the two ratios with exact decimal division, which in MySQL is exact enough for these small counts. Both read the table once or twice and sort a few groups.
Reference solution (MySQL)
WITH by_size AS (
SELECT article_type, size,
COUNT(*) AS items,
SUM(CASE WHEN outcome = 'size_return' THEN 1 ELSE 0 END) AS size_returns
FROM OrderItem
GROUP BY article_type, size
), with_totals AS (
SELECT article_type, size, items, size_returns,
SUM(items) OVER (PARTITION BY article_type) AS article_items,
SUM(size_returns) OVER (PARTITION BY article_type) AS article_returns
FROM by_size
)
SELECT article_type, size, items, size_returns
FROM with_totals
WHERE article_returns > 0
AND size_returns * article_items > items * article_returnsAnother way
SELECT s.article_type, s.size, s.items, s.size_returns
FROM (SELECT article_type, size, COUNT(*) AS items, SUM(outcome = 'size_return') AS size_returns
FROM OrderItem GROUP BY article_type, size) s
JOIN (SELECT article_type, COUNT(*) AS article_items, SUM(outcome = 'size_return') AS article_returns
FROM OrderItem GROUP BY article_type) a ON a.article_type = s.article_type
WHERE a.article_returns > 0
AND s.size_returns / a.article_returns > s.items / a.article_items← Myntra: Each Brand's Three Most-Wishlisted Styles in One Row · Myntra: The Few Brands Making Up 80% of Each Category's Sale Revenue →