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
| Column | Type |
|---|---|
| line_id | int |
| category | varchar |
| brand | varchar |
| sold_on | date |
| gmv | int |
| is_cancelled | bool |
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_id | category | brand | sold_on | gmv | is_cancelled |
|---|---|---|---|---|---|
| 1 | Mobiles | Nova | 2024-09-27 | 30000 | 0 |
| 2 | Mobiles | Nova | 2024-09-30 | 20000 | 0 |
| 3 | Mobiles | Pixelon | 2024-09-28 | 42000 | 0 |
| 4 | Mobiles | Redstar | 2024-10-01 | 22000 | 0 |
| 5 | Mobiles | Redstar | 2024-10-06 | 20000 | 0 |
| 6 | Mobiles | Moto | 2024-10-02 | 9000 | 0 |
| 7 | Mobiles | Moto | 2024-10-07 | 50000 | 0 |
| 8 | Mobiles | Realm | 2024-10-03 | 3000 | 0 |
| 9 | Mobiles | Realm | 2024-10-03 | 15000 | 1 |
| 10 | Fashion | UrbanThreads | 2024-09-29 | 5000 | 0 |
| 11 | Fashion | Desi Weaves | 2024-10-04 | 7000 | 0 |
| 12 | Audio | BassBuds | 2024-09-27 | 8000 | 0 |
| 13 | Audio | Thump | 2024-10-05 | 8000 | 0 |
| 14 | Audio | Sonic | 2024-09-29 | 2000 | 0 |
| 15 | Audio | Echo | 2024-10-02 | 1000 | 0 |
Output
| category | brand | gmv |
|---|---|---|
| Audio | BassBuds | 8000 |
| Audio | Thump | 8000 |
| Audio | Sonic | 2000 |
| Audio | Others | 1000 |
| Fashion | Desi Weaves | 7000 |
| Fashion | UrbanThreads | 5000 |
| Mobiles | Nova | 50000 |
| Mobiles | Pixelon | 42000 |
| Mobiles | Redstar | 42000 |
| Mobiles | Others | 12000 |
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_keyAnother 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 →