Myntra: The Few Brands Making Up 80% of Each Category's Sale Revenue

Hard
821.

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

ColumnType
style_idint
brandvarchar
categoryvarchar

Primary key: style_id.

One row per style; a brand can have styles in several categories.

Table: SaleOrderLine

ColumnTypeValues
line_idint
style_idint
amountint
statusenumdelivered, returned, cancelled

Primary key: line_id.

One row per item sold during the sale, at the price paid in rupees.

Example

The rows must come back in the order shown.

Style

style_idbrandcategory
1RoadsterJeans
2RoadsterJeans
3HRXJeans
4Mast & HarbourJeans
5Here&NowJeans
6AnoukKurta
7SangriaKurta
8DressberryKurta

SaleOrderLine

line_idstyle_idamountstatus
113000delivered
222000delivered
333000delivered
441000delivered
551000delivered
664000delivered
774000delivered
881000delivered
985000returned
1032500cancelled

Output

categorybrandrevenuecumulative_revenue
JeansRoadster50005000
JeansHRX30008000
KurtaAnouk40004000
KurtaSangria40008000
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: 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

ColumnType
style_idint
brandvarchar
categoryvarchar

Primary key: style_id.

One row per style; a brand can have styles in several categories.

Table: SaleOrderLine

ColumnType
line_idint
style_idint
amountint
statusenum(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_idbrandcategory
1RoadsterJeans
2RoadsterJeans
3HRXJeans
4Mast & HarbourJeans
5Here&NowJeans
6AnoukKurta
7SangriaKurta
8DressberryKurta

SaleOrderLine

line_idstyle_idamountstatus
113000delivered
222000delivered
333000delivered
441000delivered
551000delivered
664000delivered
774000delivered
881000delivered
985000returned
1032500cancelled

Output

categorybrandrevenuecumulative_revenue
JeansRoadster50005000
JeansHRX30008000
KurtaAnouk40004000
KurtaSangria40008000

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

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