Myntra: The Few Brands Making Up 80% of Each Category's Sale Revenue
After the sale, category managers want to know how concentrated their revenue was: which leading brands, taken from the biggest down, together make up 80%…
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Joins
- Dialect: MySQL
- Problem: #821
Problem statement
After the sale, category managers want to know how concentrated their revenue was: which leading brands, taken from the biggest down, together make up 80% of the category's revenue.
A brand's revenue in a category is the total amount of its delivered lines on styles of that category. Within each category, rank the brands with revenue by revenue descending, ties broken by brand ascending. Return a brand when the brands ranked before it make up less than 80% of the category's revenue — so the list runs up to and including the brand that reaches 80%. Return category, brand, revenue and cumulative_revenue (the revenue of the brands up to and including this one), ordered by category, then revenue descending, then brand.
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; a brand can have styles in several categories.
Table: SaleOrderLine
| 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, at the price paid in rupees.
Examples
Example 1
Style
| style_id | brand | category |
|---|---|---|
| 1 | Roadster | Jeans |
| 2 | Roadster | Jeans |
| 3 | HRX | Jeans |
| 4 | Mast & Harbour | Jeans |
| 5 | Here&Now | Jeans |
| 6 | Anouk | Kurta |
| 7 | Sangria | Kurta |
| 8 | Dressberry | Kurta |
SaleOrderLine
| line_id | style_id | amount | status |
|---|---|---|---|
| 1 | 1 | 3000 | delivered |
| 2 | 2 | 2000 | delivered |
| 3 | 3 | 3000 | delivered |
| 4 | 4 | 1000 | delivered |
| 5 | 5 | 1000 | delivered |
| 6 | 6 | 4000 | delivered |
| 7 | 7 | 4000 | delivered |
| 8 | 8 | 1000 | delivered |
| 9 | 8 | 5000 | returned |
| 10 | 3 | 2500 | cancelled |
Output
| category | brand | revenue | cumulative_revenue |
|---|---|---|---|
| Jeans | Roadster | 5000 | 5000 |
| Jeans | HRX | 3000 | 8000 |
| Kurta | Anouk | 4000 | 4000 |
| Kurta | Sangria | 4000 | 8000 |
How to solve Myntra: The Few Brands Making Up 80% of Each Category's Sale Revenue
This is a Pareto cut: the shortest list of top brands that together reach 80% of a category's revenue. It takes three steps. First, aggregate: delivered lines joined to their style's brand and category, summed per (category, brand). Returned and cancelled lines never count, which is why Dressberry's ₹5000 return does not make it the biggest kurta brand.
Second, a window over the aggregate: SUM(revenue) OVER (PARTITION BY category ORDER BY revenue DESC, brand ROWS …) is the running revenue down the ranking, and SUM(revenue) OVER (PARTITION BY category) the category total on every row. The brand tie-break and the ROWS frame matter: Here&Now and Mast & Harbour both made ₹1000 in jeans, and without them the two would share one running value.
Third, the cut. A brand belongs to the list when the brands before it have not yet reached 80% — cumulative_revenue - revenue below 80% of the total — which keeps the brand that crosses the line and nothing after it. Written as (cumulative_revenue - revenue) * 10 < category_revenue * 8 it is exact; a brand whose predecessors make exactly 80% is not needed and is left out, as Here&Now is. The alternative computes the revenue ahead of each brand with a correlated sum over the brand totals, using the same tie order, and is quadratic per category where the window version is one sort.
Reference solution (MySQL)
WITH brand_revenue AS (
SELECT s.category, s.brand, SUM(l.amount) AS revenue
FROM SaleOrderLine l
JOIN Style s ON s.style_id = l.style_id
WHERE l.status = 'delivered'
GROUP BY s.category, s.brand
), running AS (
SELECT category, brand, revenue,
SUM(revenue) OVER (PARTITION BY category ORDER BY revenue DESC, brand
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue,
SUM(revenue) OVER (PARTITION BY category) AS category_revenue
FROM brand_revenue
)
SELECT category, brand, revenue, cumulative_revenue
FROM running
WHERE (cumulative_revenue - revenue) * 10 < category_revenue * 8
ORDER BY category, revenue DESC, brandAnother way
WITH br AS (
SELECT s.category, s.brand, SUM(l.amount) AS revenue
FROM SaleOrderLine l, Style s
WHERE s.style_id = l.style_id AND l.status IN ('delivered')
GROUP BY s.category, s.brand
), placed AS (
SELECT b.category, b.brand, b.revenue,
(SELECT COALESCE(SUM(a.revenue), 0) FROM br a
WHERE a.category = b.category
AND (a.revenue > b.revenue OR (a.revenue = b.revenue AND a.brand < b.brand))) AS ahead,
(SELECT SUM(t.revenue) FROM br t WHERE t.category = b.category) AS total
FROM br b
)
SELECT category, brand, revenue, ahead + revenue AS cumulative_revenue
FROM placed
WHERE ahead < 0.8 * total
ORDER BY category, revenue DESC, brand← Myntra: Sizes Over-Represented in Size Returns per Article Type · Myntra: Serial Returners by Their Last Five Delivered Orders →