Myntra: Sizes Over-Represented in Size Returns per Article Type

Hard
820.

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

ColumnTypeValues
order_item_idint
article_typevarchar
sizevarchar
outcomeenumkept, 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.

Example

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

OrderItem

order_item_idarticle_typesizeoutcome
1Jeans30kept
2Jeans30kept
3Jeans30size_return
4Jeans30kept
5Jeans32size_return
6Jeans32size_return
7Jeans32size_return
8Jeans32kept
9Jeans34kept
10Jeans34other_return
6 more rows

Output

article_typesizeitemssize_returns
Jeans3243
KurtaL11
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: 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

ColumnType
order_item_idint
article_typevarchar
sizevarchar
outcomeenum(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_idarticle_typesizeoutcome
1Jeans30kept
2Jeans30kept
3Jeans30size_return
4Jeans30kept
5Jeans32size_return
6Jeans32size_return
7Jeans32size_return
8Jeans32kept
9Jeans34kept
10Jeans34other_return
11KurtaMsize_return
12KurtaMother_return
13KurtaMkept
14KurtaLsize_return
15Sneakers8other_return
16Sneakers8other_return

Output

article_typesizeitemssize_returns
Jeans3243
KurtaL11

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_returns

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