Flipkart: Running GMV Through the Sale by Category

Medium
665.

Flipkart: Running GMV Through the Sale by Category

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

ColumnType
item_idint
categoryvarchar
sold_ondate
selling_priceint
quantityint
is_cancelledbool

Primary key: item_id.

One row per item sold; is_cancelled = 1 when the buyer or seller cancelled it.

Example

The rows must come back in the order shown.

SaleItem

item_idcategorysold_onselling_pricequantityis_cancelled
1Mobiles2024-09-261500010
2Mobiles2024-09-271200010
3Mobiles2024-09-27900020
4Mobiles2024-09-291400011
5Mobiles2024-10-011100010
6Fashion2024-09-2879930
7Fashion2024-10-06129910

Output

categorysold_onday_gmvrunning_gmv
Fashion2024-09-2823972397
Fashion2024-10-0612993696
Mobiles2024-09-273000030000
Mobiles2024-10-011100041000
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: 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

ColumnType
item_idint
categoryvarchar
sold_ondate
selling_priceint
quantityint
is_cancelledbool

Primary key: item_id.

One row per item sold; is_cancelled = 1 when the buyer or seller cancelled it.

Examples

Example 1

SaleItem

item_idcategorysold_onselling_pricequantityis_cancelled
1Mobiles2024-09-261500010
2Mobiles2024-09-271200010
3Mobiles2024-09-27900020
4Mobiles2024-09-291400011
5Mobiles2024-10-011100010
6Fashion2024-09-2879930
7Fashion2024-10-06129910

Output

categorysold_onday_gmvrunning_gmv
Fashion2024-09-2823972397
Fashion2024-10-0612993696
Mobiles2024-09-273000030000
Mobiles2024-10-011100041000

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_on

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

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