Myntra: Size-Related Return Rate by Article Type and Size

Medium
806.

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.

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

ColumnType
style_idint
brandvarchar
article_typevarchar

Primary key: style_id.

One row per style.

Table: OrderItem

ColumnType
order_item_idint
style_idint
sizevarchar

Primary key: order_item_id.

One row per item sold, in one size.

Table: ReturnRequest

ColumnType
return_idint
order_item_idint
reasonvarchar

Primary key: return_id.

At most one request per item.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Style

style_idbrandarticle_type
1RoadsterJeans
2HRXT-shirt
3SangriaJeans

OrderItem

order_item_idstyle_idsize
11M
23M
31M
41L
53L
62M
72M
82M
92M
102S

ReturnRequest

return_idorder_item_idreason
11size_too_small
22damaged
34size_too_large
46size_too_large
57size_too_small
68colour_different

Output

article_typesizeitemssize_returnssize_return_pct
JeansM3133.3
T-shirtM4250
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-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

ColumnType
style_idint
brandvarchar
article_typevarchar

Primary key: style_id.

One row per style.

Table: OrderItem

ColumnType
order_item_idint
style_idint
sizevarchar

Primary key: order_item_id.

One row per item sold, in one size.

Table: ReturnRequest

ColumnType
return_idint
order_item_idint
reasonvarchar

Primary key: return_id.

At most one request per item.

Examples

Example 1

Style

style_idbrandarticle_type
1RoadsterJeans
2HRXT-shirt
3SangriaJeans

OrderItem

order_item_idstyle_idsize
11M
23M
31M
41L
53L
62M
72M
82M
92M
102S

ReturnRequest

return_idorder_item_idreason
11size_too_small
22damaged
34size_too_large
46size_too_large
57size_too_small
68colour_different

Output

article_typesizeitemssize_returnssize_return_pct
JeansM3133.3
T-shirtM4250

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(*) >= 3

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