Flipkart: Running GMV Through the Sale by Category
The Big Billion Days dashboard shows each category's GMV piling up day by day.
- Difficulty: Medium
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #665
Problem statement
The Big Billion Days dashboard shows each category's GMV piling up day by day. GMV is selling_price * quantity over items that were not cancelled, sold within the sale (2024-09-27 to 2024-10-06, inclusive).
For each category and each sale day on which it had GMV, return category, sold_on, day_gmv (that day's GMV) and running_gmv (the category's GMV from the start of the sale up to and including that day). Days without sales are not listed. Order by category, then sold_on.
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: SaleItem
| Column | Type |
|---|---|
| item_id | int |
| category | varchar |
| sold_on | date |
| selling_price | int |
| quantity | int |
| is_cancelled | bool |
Primary key: item_id.
One row per item sold; is_cancelled = 1 when the buyer or seller cancelled it.
Examples
Example 1
SaleItem
| item_id | category | sold_on | selling_price | quantity | is_cancelled |
|---|---|---|---|---|---|
| 1 | Mobiles | 2024-09-26 | 15000 | 1 | 0 |
| 2 | Mobiles | 2024-09-27 | 12000 | 1 | 0 |
| 3 | Mobiles | 2024-09-27 | 9000 | 2 | 0 |
| 4 | Mobiles | 2024-09-29 | 14000 | 1 | 1 |
| 5 | Mobiles | 2024-10-01 | 11000 | 1 | 0 |
| 6 | Fashion | 2024-09-28 | 799 | 3 | 0 |
| 7 | Fashion | 2024-10-06 | 1299 | 1 | 0 |
Output
| category | sold_on | day_gmv | running_gmv |
|---|---|---|---|
| Fashion | 2024-09-28 | 2397 | 2397 |
| Fashion | 2024-10-06 | 1299 | 3696 |
| Mobiles | 2024-09-27 | 30000 | 30000 |
| Mobiles | 2024-10-01 | 11000 | 41000 |
How to solve Flipkart: Running GMV Through the Sale by Category
There are two levels of summing. First the items collapse to one row per category and day — daily, with the cancelled items and the days outside the sale filtered in WHERE so they never enter a total. Then a window adds those daily figures cumulatively: SUM(day_gmv) OVER (PARTITION BY category ORDER BY sold_on) restarts for each category and, for each day, sums every day up to and including it.
With an ORDER BY in the window the default frame runs from the partition's start to the current row (and its peers). Because daily has exactly one row per category and day there are no peers, so the default and an explicit ROWS UNBOUNDED PRECEDING agree. The third query nests the aggregate inside the window in a single GROUP BY, which is legal: the window runs after grouping.
The correlated-subquery alternative re-adds the earlier days for every row — quadratic in the number of days per category, but tiny here. The window version is one sorted pass.
Reference solution (MySQL)
WITH daily AS (
SELECT category, sold_on, SUM(selling_price * quantity) AS day_gmv
FROM SaleItem
WHERE is_cancelled = 0 AND sold_on BETWEEN '2024-09-27' AND '2024-10-06'
GROUP BY category, sold_on
)
SELECT category, sold_on, day_gmv,
SUM(day_gmv) OVER (PARTITION BY category ORDER BY sold_on) AS running_gmv
FROM daily
ORDER BY category, sold_onAnother way
WITH daily AS (
SELECT category, sold_on, SUM(selling_price * quantity) AS day_gmv
FROM SaleItem WHERE is_cancelled = 0 AND sold_on >= '2024-09-27' AND sold_on <= '2024-10-06'
GROUP BY category, sold_on
)
SELECT d.category, d.sold_on, d.day_gmv,
(SELECT SUM(e.day_gmv) FROM daily e WHERE e.category = d.category AND e.sold_on <= d.sold_on) AS running_gmv
FROM daily d ORDER BY d.category, d.sold_onAnother way
SELECT category, sold_on, SUM(selling_price * quantity) AS day_gmv,
SUM(SUM(selling_price * quantity)) OVER (PARTITION BY category ORDER BY sold_on ROWS UNBOUNDED PRECEDING) AS running_gmv
FROM SaleItem
WHERE is_cancelled = 0 AND sold_on BETWEEN '2024-09-27' AND '2024-10-06'
GROUP BY category, sold_on
ORDER BY category, sold_on← Flipkart: Pay Later Bills Not Cleared by the Due Date · Flipkart: SuperCoins Left in Each Earn Batch After FIFO Burns →