Flipkart: Top Three Brands per Category Plus an Others Line

Medium
663.

Flipkart: Top Three Brands per Category Plus an Others Line

The Big Billion Days recap deck shows each category's brand split as a short table: the three biggest brands by name, and everything else folded into one Others line so the table stays readable.

Count GMV from lines that are not cancelled and were sold within the sale (2024-09-27 to 2024-10-06, inclusive). In each category, rank brands by GMV, highest first, ties to the brand name alphabetically. Return category, brand and gmv: one row for each of the top 3 brands, then one row with brand = 'Others' holding the combined GMV of all lower-ranked brands — only if the category has more than three brands. Order by category, then the top brands in rank order, with Others last.

A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.

Tables

Table: SaleOrderLine

ColumnType
line_idint
categoryvarchar
brandvarchar
sold_ondate
gmvint
is_cancelledbool

Primary key: line_id.

One row per order line, with its GMV in rupees. No brand is literally named 'Others'.

Example

The rows must come back in the order shown.

SaleOrderLine

line_idcategorybrandsold_ongmvis_cancelled
1MobilesNova2024-09-27300000
2MobilesNova2024-09-30200000
3MobilesPixelon2024-09-28420000
4MobilesRedstar2024-10-01220000
5MobilesRedstar2024-10-06200000
6MobilesMoto2024-10-0290000
7MobilesMoto2024-10-07500000
8MobilesRealm2024-10-0330000
9MobilesRealm2024-10-03150001
10FashionUrbanThreads2024-09-2950000
5 more rows

Output

categorybrandgmv
AudioBassBuds8000
AudioThump8000
AudioSonic2000
AudioOthers1000
FashionDesi Weaves7000
FashionUrbanThreads5000
MobilesNova50000
MobilesPixelon42000
MobilesRedstar42000
MobilesOthers12000
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.

Flipkart: Top Three Brands per Category Plus an Others Line

The Big Billion Days recap deck shows each category's brand split as a short table: the three biggest brands by name, and everything else folded into one…

  • Difficulty: Medium
  • Topics: Window Functions, Aggregation
  • Dialect: MySQL
  • Problem: #663

Problem statement

The Big Billion Days recap deck shows each category's brand split as a short table: the three biggest brands by name, and everything else folded into one Others line so the table stays readable.

Count GMV from lines that are not cancelled and were sold within the sale (2024-09-27 to 2024-10-06, inclusive). In each category, rank brands by GMV, highest first, ties to the brand name alphabetically. Return category, brand and gmv: one row for each of the top 3 brands, then one row with brand = 'Others' holding the combined GMV of all lower-ranked brands — only if the category has more than three brands. Order by category, then the top brands in rank order, with Others last.

A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.

Tables

Table: SaleOrderLine

ColumnType
line_idint
categoryvarchar
brandvarchar
sold_ondate
gmvint
is_cancelledbool

Primary key: line_id.

One row per order line, with its GMV in rupees. No brand is literally named 'Others'.

Examples

Example 1

SaleOrderLine

line_idcategorybrandsold_ongmvis_cancelled
1MobilesNova2024-09-27300000
2MobilesNova2024-09-30200000
3MobilesPixelon2024-09-28420000
4MobilesRedstar2024-10-01220000
5MobilesRedstar2024-10-06200000
6MobilesMoto2024-10-0290000
7MobilesMoto2024-10-07500000
8MobilesRealm2024-10-0330000
9MobilesRealm2024-10-03150001
10FashionUrbanThreads2024-09-2950000
11FashionDesi Weaves2024-10-0470000
12AudioBassBuds2024-09-2780000
13AudioThump2024-10-0580000
14AudioSonic2024-09-2920000
15AudioEcho2024-10-0210000

Output

categorybrandgmv
AudioBassBuds8000
AudioThump8000
AudioSonic2000
AudioOthers1000
FashionDesi Weaves7000
FashionUrbanThreads5000
MobilesNova50000
MobilesPixelon42000
MobilesRedstar42000
MobilesOthers12000

How to solve Flipkart: Top Three Brands per Category Plus an Others Line

A top-N table with a remainder line is two aggregations stacked. The first totals GMV per category and brand, after filtering cancelled lines and anything outside the inclusive sale window. The second decides what each brand becomes: ROW_NUMBER() per category, by GMV descending with the brand name breaking ties, gives every brand a position, and positions 1–3 stay themselves while every brand after them is folded into Others.

The solution writes the two kinds of row as two SELECTs joined by UNION ALL: the top three as they are, and one grouped row of everything with rn > 3. A category with three brands or fewer has no such rows, so the grouped SELECT produces no Others line for it — exactly as required, with no empty Others row showing 0. A sort_key (the position, and 4 for Others) carries the order out of the union; sorting by GMV would put a large Others line above the brands.

The first alternative renames instead: CASE WHEN rn <= 3 THEN brand ELSE 'Others' END is grouped a second time, and MIN(rn) orders the groups. The second ranks with a correlated count instead of a window. Each is a grouping of the lines plus a sort of the brands per category.

Reference solution (MySQL)

WITH brand_gmv AS (
  SELECT category, brand, SUM(gmv) AS gmv
  FROM SaleOrderLine
  WHERE is_cancelled = 0 AND sold_on BETWEEN '2024-09-27' AND '2024-10-06'
  GROUP BY category, brand
), ranked AS (
  SELECT category, brand, gmv,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY gmv DESC, brand) AS rn
  FROM brand_gmv
)
SELECT category, brand, gmv
FROM (
  SELECT category, brand, gmv, rn AS sort_key FROM ranked WHERE rn <= 3
  UNION ALL
  SELECT category, 'Others', SUM(gmv), 4 FROM ranked WHERE rn > 3 GROUP BY category
) t
ORDER BY category, sort_key

Another way

WITH brand_gmv AS (
  SELECT category, brand, SUM(gmv) AS gmv FROM SaleOrderLine
  WHERE is_cancelled = 0 AND sold_on >= '2024-09-27' AND sold_on <= '2024-10-06'
  GROUP BY category, brand
), ranked AS (
  SELECT category, brand, gmv,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY gmv DESC, brand) AS rn
  FROM brand_gmv
)
SELECT category, CASE WHEN rn <= 3 THEN brand ELSE 'Others' END AS brand, SUM(gmv) AS gmv
FROM ranked
GROUP BY category, CASE WHEN rn <= 3 THEN brand ELSE 'Others' END
ORDER BY category, MIN(rn)

Another way

WITH brand_gmv AS (
  SELECT category, brand, SUM(gmv) AS gmv FROM SaleOrderLine
  WHERE is_cancelled = 0 AND sold_on BETWEEN '2024-09-27' AND '2024-10-06'
  GROUP BY category, brand
), ranked AS (
  SELECT a.category, a.brand, a.gmv,
         1 + (SELECT COUNT(*) FROM brand_gmv b WHERE b.category = a.category
              AND (b.gmv > a.gmv OR (b.gmv = a.gmv AND b.brand < a.brand))) AS pos
  FROM brand_gmv a
)
SELECT category, brand, gmv FROM (
  SELECT category, IF(pos <= 3, brand, 'Others') AS brand, SUM(gmv) AS gmv, MIN(pos) AS first_pos
  FROM ranked GROUP BY category, IF(pos <= 3, brand, 'Others')
) t
ORDER BY category, first_pos

← Flipkart: Sale Shoppers Who Bought From Three Categories · Flipkart: Pay Later Bills Not Cleared by the Due Date →