Myntra: Each Brand's Share of Its Category in the EORS
After the End of Reason Sale, each category manager gets a page showing how the category's revenue split across its brands, and the head of merchandising…
- Difficulty: Hard
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #816
Problem statement
After the End of Reason Sale, each category manager gets a page showing how the category's revenue split across its brands, and the head of merchandising wants to see how big each category was within the whole sale.
Revenue is the sum of amount over order lines with status = 'delivered'; a style's category and brand come from Style. For every (category, brand) pair with revenue above 0, return category, brand, revenue, pct_of_category — the pair's revenue as a percentage of its category's revenue — and category_pct_of_sale — the whole category's revenue as a percentage of all delivered revenue in the sale (the same value on every row of a category). Round both percentages to 2 decimal places. 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
| Column | Type |
|---|---|
| style_id | int |
| brand | varchar |
| category | varchar |
Primary key: style_id.
One row per style in the sale catalogue.
Table: SaleLine
| Column | Type |
|---|---|
| line_id | int |
| style_id | int |
| amount | int |
| status | enum(delivered, returned, cancelled) |
Primary key: line_id.
One row per item sold during the sale, in rupees. Only delivered lines are revenue.
Examples
Example 1
Style
| style_id | brand | category |
|---|---|---|
| 1 | Roadster | Men Topwear |
| 2 | Roadster | Men Topwear |
| 3 | HRX | Men Topwear |
| 4 | Anouk | Ethnic Wear |
| 5 | Sangria | Ethnic Wear |
| 6 | HRX | Footwear |
| 7 | Roadster | Footwear |
SaleLine
| line_id | style_id | amount | status |
|---|---|---|---|
| 1 | 1 | 1200 | delivered |
| 2 | 2 | 1800 | delivered |
| 3 | 3 | 1000 | delivered |
| 4 | 3 | 900 | returned |
| 5 | 4 | 2500 | delivered |
| 6 | 5 | 1500 | delivered |
| 7 | 5 | 700 | cancelled |
| 8 | 6 | 2000 | delivered |
| 9 | 7 | 600 | returned |
Output
| category | brand | revenue | pct_of_category | category_pct_of_sale |
|---|---|---|---|---|
| Ethnic Wear | Anouk | 2500 | 62.5 | 40 |
| Ethnic Wear | Sangria | 1500 | 37.5 | 40 |
| Footwear | HRX | 2000 | 100 | 20 |
| Men Topwear | HRX | 1000 | 25 | 40 |
| Men Topwear | Roadster | 3000 | 75 | 40 |
How to solve Myntra: Each Brand's Share of Its Category in the EORS
Two levels of totals are needed on one row: the brand's own revenue, its category's (the parent) and the sale's. Grouping can give any one of them, but a share needs the brand and its parent side by side — which is exactly what a window aggregate does. After grouping to one row per (category, brand), SUM(revenue) OVER (PARTITION BY category) adds up the brands of the same category and writes that total on each of their rows, and SUM(revenue) OVER () writes the grand total on every row. Dividing gives both percentages without a second pass or a join.
The filters decide what the parents contain. Only delivered lines count, so HRX's returned topwear line and Sangria's cancelled one add nothing, and Roadster's footwear, with only a returned line, has no revenue and no row — and it does not dilute Footwear, where HRX holds 100%. Roadster's two topwear styles merge into one 3,000-rupee brand row, 75% of Men Topwear. The percentages of a category add to 100 (up to rounding) and the categories' shares of the sale add to 100 too.
The first alternative computes the category totals and the grand total in their own grouped CTEs and joins them back — the classic pre-window approach. The second recomputes each total with correlated subqueries per row, which is simple to read but rescans the lines per group. The window version sorts the grouped rows once by category.
Reference solution (MySQL)
WITH pair AS (
SELECT s.category, s.brand, SUM(l.amount) AS revenue
FROM SaleLine l
JOIN Style s ON s.style_id = l.style_id
WHERE l.status = 'delivered'
GROUP BY s.category, s.brand
)
SELECT category, brand, revenue,
ROUND(100 * revenue / SUM(revenue) OVER (PARTITION BY category), 2) AS pct_of_category,
ROUND(100 * SUM(revenue) OVER (PARTITION BY category) / SUM(revenue) OVER (), 2) AS category_pct_of_sale
FROM pair
WHERE revenue > 0Another way
WITH pair AS (
SELECT s.category, s.brand, SUM(l.amount) AS revenue
FROM SaleLine l JOIN Style s ON s.style_id = l.style_id
WHERE l.status = 'delivered'
GROUP BY s.category, s.brand
), cat AS (
SELECT category, SUM(revenue) AS cat_revenue FROM pair GROUP BY category
), total AS (
SELECT SUM(revenue) AS sale_revenue FROM pair
)
SELECT p.category, p.brand, p.revenue,
ROUND(100 * p.revenue / c.cat_revenue, 2) AS pct_of_category,
ROUND(100 * c.cat_revenue / t.sale_revenue, 2) AS category_pct_of_sale
FROM pair p
JOIN cat c ON c.category = p.category
CROSS JOIN total tAnother way
SELECT s.category, s.brand, SUM(l.amount) AS revenue,
ROUND(100 * SUM(l.amount) / (SELECT SUM(l2.amount) FROM SaleLine l2 JOIN Style s2 ON s2.style_id = l2.style_id
WHERE l2.status = 'delivered' AND s2.category = s.category), 2) AS pct_of_category,
ROUND(100 * (SELECT SUM(l3.amount) FROM SaleLine l3 JOIN Style s3 ON s3.style_id = l3.style_id
WHERE l3.status = 'delivered' AND s3.category = s.category)
/ (SELECT SUM(l4.amount) FROM SaleLine l4 WHERE l4.status = 'delivered'), 2) AS category_pct_of_sale
FROM SaleLine l
JOIN Style s ON s.style_id = l.style_id
WHERE l.status = 'delivered'
GROUP BY s.category, s.brand← Myntra: Styles Most Shoppers Say Run Small · Myntra: The Best Price Box for Each Style Across Three Offers →