Myntra: Size Exchanges Blocked by an Unavailable Size, per Brand
A shopper who asks to exchange an item for another size can only get one if that size of the same style is in stock; otherwise the exchange turns into a…
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #813
Problem statement
A shopper who asks to exchange an item for another size can only get one if that size of the same style is in stock; otherwise the exchange turns into a refund, and the sale is usually lost. Brand managers want to see how often that happens to their brand.
An exchange request is blocked when the style has no SKU in the requested to_size, or has one whose stock is 0. For every brand with at least one exchange request, return brand, exchanges (its requests) and blocked (how many of them are blocked), ordered by blocked descending, then brand ascending.
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 |
Primary key: style_id.
One row per style; a brand has many styles.
Table: Sku
| Column | Type |
|---|---|
| style_id | int |
| size | varchar |
| stock | int |
Primary key: style_id, size.
One row per size a style is made in, with the units in stock now. A size the style is not made in has no row.
Table: SizeExchange
| Column | Type |
|---|---|
| exchange_id | int |
| style_id | int |
| from_size | varchar |
| to_size | varchar |
| requested_on | date |
Primary key: exchange_id.
One row per open exchange request: the shopper has from_size and wants to_size of the same style.
Examples
Example 1
Style
| style_id | brand |
|---|---|
| 1 | Roadster |
| 2 | Roadster |
| 3 | HRX |
| 4 | Anouk |
| 5 | Sangria |
Sku
| style_id | size | stock |
|---|---|---|
| 1 | M | 4 |
| 1 | L | 0 |
| 2 | 32 | 3 |
| 3 | S | 2 |
| 3 | M | 1 |
| 4 | XL | 0 |
| 4 | L | 6 |
SizeExchange
| exchange_id | style_id | from_size | to_size | requested_on |
|---|---|---|---|---|
| 1 | 1 | S | M | 2024-06-03 |
| 2 | 1 | M | L | 2024-06-04 |
| 3 | 2 | 30 | 34 | 2024-06-04 |
| 4 | 3 | M | S | 2024-06-05 |
| 5 | 3 | S | M | 2024-06-05 |
| 6 | 4 | L | XL | 2024-06-06 |
Output
| brand | exchanges | blocked |
|---|---|---|
| Roadster | 3 | 2 |
| Anouk | 1 | 1 |
| HRX | 2 | 0 |
How to solve Myntra: Size Exchanges Blocked by an Unavailable Size, per Brand
Each exchange request has to be checked against one SKU: the same style in the requested to_size. That is a join on two columns, k.style_id = e.style_id AND k.size = e.to_size, and it must be a LEFT JOIN, because a request for a size the style is not made in (style 2 in 34) has no SKU row at all. An inner join would throw that request away and undercount both columns; with the LEFT JOIN it stays, with NULL stock.
A request is blocked in two situations — no SKU (stock IS NULL) or an empty one (stock = 0) — and SUM(CASE WHEN … THEN 1 ELSE 0 END) counts both, while COUNT(*) counts every request. Because (style_id, size) is the key of Sku, the join never duplicates a request. The brand comes from Style by an inner join, and grouping by brand adds up across its styles; a brand with no requests forms no group, as asked.
The order is most blocked first, then brand name. The alternative asks the question the other way round — is there an SKU of that size with stock above 0? — using NOT EXISTS inside IF, which covers both the missing and the empty case in one test. Both read each request once with a key lookup into Sku.
Reference solution (MySQL)
SELECT s.brand,
COUNT(*) AS exchanges,
SUM(CASE WHEN k.stock IS NULL OR k.stock = 0 THEN 1 ELSE 0 END) AS blocked
FROM SizeExchange e
JOIN Style s ON s.style_id = e.style_id
LEFT JOIN Sku k ON k.style_id = e.style_id AND k.size = e.to_size
GROUP BY s.brand
ORDER BY blocked DESC, s.brand ASCAnother way
SELECT s.brand, COUNT(*) AS exchanges,
SUM(IF(NOT EXISTS (SELECT 1 FROM Sku k WHERE k.style_id = e.style_id AND k.size = e.to_size AND k.stock > 0), 1, 0)) AS blocked
FROM SizeExchange e, Style s
WHERE s.style_id = e.style_id
GROUP BY s.brand
ORDER BY blocked DESC, s.brand← Myntra: Jeans Buyers Whose Waist Size Changed Between Orders · Myntra: Monthly Net Revenue After Refunds in 2024 →