Myntra: Each Brand's Share of Its Category in the EORS

Hard
816.

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

ColumnType
style_idint
brandvarchar
categoryvarchar

Primary key: style_id.

One row per style in the sale catalogue.

Table: SaleLine

ColumnTypeValues
line_idint
style_idint
amountint
statusenumdelivered, returned, cancelled

Primary key: line_id.

One row per item sold during the sale, in rupees. Only delivered lines are revenue.

Example

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

Style

style_idbrandcategory
1RoadsterMen Topwear
2RoadsterMen Topwear
3HRXMen Topwear
4AnoukEthnic Wear
5SangriaEthnic Wear
6HRXFootwear
7RoadsterFootwear

SaleLine

line_idstyle_idamountstatus
111200delivered
221800delivered
331000delivered
43900returned
542500delivered
651500delivered
75700cancelled
862000delivered
97600returned

Output

categorybrandrevenuepct_of_categorycategory_pct_of_sale
Ethnic WearAnouk250062.540
Ethnic WearSangria150037.540
FootwearHRX200010020
Men TopwearHRX10002540
Men TopwearRoadster30007540
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: 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

ColumnType
style_idint
brandvarchar
categoryvarchar

Primary key: style_id.

One row per style in the sale catalogue.

Table: SaleLine

ColumnType
line_idint
style_idint
amountint
statusenum(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_idbrandcategory
1RoadsterMen Topwear
2RoadsterMen Topwear
3HRXMen Topwear
4AnoukEthnic Wear
5SangriaEthnic Wear
6HRXFootwear
7RoadsterFootwear

SaleLine

line_idstyle_idamountstatus
111200delivered
221800delivered
331000delivered
43900returned
542500delivered
651500delivered
75700cancelled
862000delivered
97600returned

Output

categorybrandrevenuepct_of_categorycategory_pct_of_sale
Ethnic WearAnouk250062.540
Ethnic WearSangria150037.540
FootwearHRX200010020
Men TopwearHRX10002540
Men TopwearRoadster30007540

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

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

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