Myntra: Size Exchanges Blocked by an Unavailable Size, per Brand

Medium
813.

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 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

ColumnType
style_idint
brandvarchar

Primary key: style_id.

One row per style; a brand has many styles.

Table: Sku

ColumnType
style_idint
sizevarchar
stockint

Primary key (together): 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

ColumnType
exchange_idint
style_idint
from_sizevarchar
to_sizevarchar
requested_ondate

Primary key: exchange_id.

One row per open exchange request: the shopper has from_size and wants to_size of the same style.

Example

The rows must come back in the order shown.

Style

style_idbrand
1Roadster
2Roadster
3HRX
4Anouk
5Sangria

Sku

style_idsizestock
1M4
1L0
2323
3S2
3M1
4XL0
4L6

SizeExchange

exchange_idstyle_idfrom_sizeto_sizerequested_on
11SM2024-06-03
21ML2024-06-04
3230342024-06-04
43MS2024-06-05
53SM2024-06-05
64LXL2024-06-06

Output

brandexchangesblocked
Roadster32
Anouk11
HRX20
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
style_idint
brandvarchar

Primary key: style_id.

One row per style; a brand has many styles.

Table: Sku

ColumnType
style_idint
sizevarchar
stockint

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

ColumnType
exchange_idint
style_idint
from_sizevarchar
to_sizevarchar
requested_ondate

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_idbrand
1Roadster
2Roadster
3HRX
4Anouk
5Sangria

Sku

style_idsizestock
1M4
1L0
2323
3S2
3M1
4XL0
4L6

SizeExchange

exchange_idstyle_idfrom_sizeto_sizerequested_on
11SM2024-06-03
21ML2024-06-04
3230342024-06-04
43MS2024-06-05
53SM2024-06-05
64LXL2024-06-06

Output

brandexchangesblocked
Roadster32
Anouk11
HRX20

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 ASC

Another 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 →