Flipkart: Big Billion Days GMV by Sale Day Against Last Year

Hard
667.

Flipkart: Big Billion Days GMV by Sale Day Against Last Year

The Big Billion Days war room compares this year's sale with last year's like for like: day 1 of the 2024 sale against day 1 of the 2023 sale, day 2 against day 2, and so on — the two sales start on different dates and need not last equally long.

Day n of a sale is the date starts_on + (n - 1) days. For every category in Category and every day number from 1 to the length of the longer sale, return category, day_no, gmv_2023 and gmv_2024: the category's total GMV on that day of that year's sale. A sale day with no sales in the category shows 0; a day number past the end of that year's sale shows NULL (the sale had no such day). Items sold outside both sales are ignored. Order by category, then day_no.

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: BigSale

ColumnType
sale_yearint
starts_ondate
ends_ondate

Primary key: sale_year.

Exactly two rows, for 2023 and 2024. Both dates are inclusive; a sale lasts 1 to 12 days.

Table: Category

ColumnType
categoryvarchar

Primary key: category.

Every category on the war-room board, whether or not it sold anything.

Table: SaleItem

ColumnType
item_idint
categoryvarchar
sold_ondate
gmvint

Primary key: item_id.

One row per item sold, with its GMV in rupees; category always names a row of Category.

Example

The rows must come back in the order shown.

BigSale

sale_yearstarts_onends_on
20232023-10-082023-10-11
20242024-09-272024-10-01

Category

category
Books
Fashion
Mobiles

SaleItem

item_idcategorysold_ongmv
1Mobiles2023-10-081000
2Mobiles2023-10-08500
3Mobiles2023-10-10800
4Mobiles2024-09-272000
5Mobiles2024-09-29700
6Mobiles2024-10-01300
7Mobiles2024-10-02999
8Fashion2023-10-11400
9Fashion2024-09-28650
10Fashion2023-10-07100

Output

categoryday_nogmv_2023gmv_2024
Books100
Books200
Books300
Books400
Books5NULL0
Fashion100
Fashion20650
Fashion300
Fashion44000
Fashion5NULL0
5 more rows
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: Big Billion Days GMV by Sale Day Against Last Year

The Big Billion Days war room compares this year's sale with last year's like for like: day 1 of the 2024 sale against day 1 of the 2023 sale, day 2 against…

  • Difficulty: Hard
  • Topics: Subqueries, Joins, Dates, Conditional Logic
  • Dialect: MySQL
  • Problem: #667

Problem statement

The Big Billion Days war room compares this year's sale with last year's like for like: day 1 of the 2024 sale against day 1 of the 2023 sale, day 2 against day 2, and so on — the two sales start on different dates and need not last equally long.

Day n of a sale is the date starts_on + (n - 1) days. For every category in Category and every day number from 1 to the length of the longer sale, return category, day_no, gmv_2023 and gmv_2024: the category's total GMV on that day of that year's sale. A sale day with no sales in the category shows 0; a day number past the end of that year's sale shows NULL (the sale had no such day). Items sold outside both sales are ignored. Order by category, then day_no.

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: BigSale

ColumnType
sale_yearint
starts_ondate
ends_ondate

Primary key: sale_year.

Exactly two rows, for 2023 and 2024. Both dates are inclusive; a sale lasts 1 to 12 days.

Table: Category

ColumnType
categoryvarchar

Primary key: category.

Every category on the war-room board, whether or not it sold anything.

Table: SaleItem

ColumnType
item_idint
categoryvarchar
sold_ondate
gmvint

Primary key: item_id.

One row per item sold, with its GMV in rupees; category always names a row of Category.

Examples

Example 1

BigSale

sale_yearstarts_onends_on
20232023-10-082023-10-11
20242024-09-272024-10-01

Category

category
Books
Fashion
Mobiles

SaleItem

item_idcategorysold_ongmv
1Mobiles2023-10-081000
2Mobiles2023-10-08500
3Mobiles2023-10-10800
4Mobiles2024-09-272000
5Mobiles2024-09-29700
6Mobiles2024-10-01300
7Mobiles2024-10-02999
8Fashion2023-10-11400
9Fashion2024-09-28650
10Fashion2023-10-07100

Output

categoryday_nogmv_2023gmv_2024
Books100
Books200
Books300
Books400
Books5NULL0
Fashion100
Fashion20650
Fashion300
Fashion44000
Fashion5NULL0
Mobiles115002000
Mobiles200
Mobiles3800700
Mobiles400
Mobiles5NULL300

How to solve Flipkart: Big Billion Days GMV by Sale Day Against Last Year

A year-over-year comparison of two sales cannot match on dates — 2023's day 1 is 8 October, 2024's is 27 September — so it matches on day numbers. Day n of a sale is DATE_ADD(starts_on, INTERVAL n - 1 DAY), or equivalently a sold date belongs to day DATEDIFF(sold_on, starts_on) + 1.

The report must show every category on every day, including Books on a day nothing sold, so the rows cannot come from the sales. They come from a grid: a recursive CTE counts day numbers from 1 up to the longer sale's length (the recursive step joins the length rather than using an aggregate, which MySQL forbids there), and a CROSS JOIN with Category gives one row per category and day. The daily totals are LEFT JOINed onto it for each year, and a missing total becomes 0.

The last detail is NULL versus 0. When the 2024 sale ran five days and the 2023 sale four, day 5 of 2023 never existed; reporting 0 there would claim a day with no sales. A CASE on day_no <= sale_days returns NULL past the end of that year's sale. The alternative uses a fixed numbers table (sales last at most 12 days), tags each in-sale item with its year and day number, and sums with correlated subqueries. Either touches each item once and the grid is categories × days.

Reference solution (MySQL)

WITH RECURSIVE span AS (
  SELECT MAX(DATEDIFF(ends_on, starts_on) + 1) AS max_days FROM BigSale
), days AS (
  SELECT 1 AS day_no
  UNION ALL
  SELECT d.day_no + 1 FROM days d JOIN span s ON d.day_no < s.max_days
), y AS (
  SELECT sale_year, starts_on, DATEDIFF(ends_on, starts_on) + 1 AS sale_days FROM BigSale
), daily AS (
  SELECT category, sold_on, SUM(gmv) AS gmv FROM SaleItem GROUP BY category, sold_on
)
SELECT c.category, d.day_no,
       CASE WHEN d.day_no <= a.sale_days THEN COALESCE(x.gmv, 0) END AS gmv_2023,
       CASE WHEN d.day_no <= b.sale_days THEN COALESCE(z.gmv, 0) END AS gmv_2024
FROM Category c
CROSS JOIN days d
JOIN y a ON a.sale_year = 2023
JOIN y b ON b.sale_year = 2024
LEFT JOIN daily x ON x.category = c.category AND x.sold_on = DATE_ADD(a.starts_on, INTERVAL d.day_no - 1 DAY)
LEFT JOIN daily z ON z.category = c.category AND z.sold_on = DATE_ADD(b.starts_on, INTERVAL d.day_no - 1 DAY)
ORDER BY c.category, d.day_no

Another way

WITH nums AS (
  SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
  UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
), tagged AS (
  SELECT i.category, i.gmv, b.sale_year, DATEDIFF(i.sold_on, b.starts_on) + 1 AS day_no
  FROM SaleItem i JOIN BigSale b ON i.sold_on BETWEEN b.starts_on AND b.ends_on
)
SELECT c.category, k.n AS day_no,
       IF(k.n <= (SELECT DATEDIFF(ends_on, starts_on) + 1 FROM BigSale WHERE sale_year = 2023),
          (SELECT COALESCE(SUM(t.gmv), 0) FROM tagged t WHERE t.category = c.category AND t.sale_year = 2023 AND t.day_no = k.n), NULL) AS gmv_2023,
       IF(k.n <= (SELECT DATEDIFF(ends_on, starts_on) + 1 FROM BigSale WHERE sale_year = 2024),
          (SELECT COALESCE(SUM(t.gmv), 0) FROM tagged t WHERE t.category = c.category AND t.sale_year = 2024 AND t.day_no = k.n), NULL) AS gmv_2024
FROM Category c CROSS JOIN nums k
WHERE k.n <= (SELECT MAX(DATEDIFF(ends_on, starts_on) + 1) FROM BigSale)
ORDER BY c.category, k.n

← Flipkart: SuperCoins Left in Each Earn Batch After FIFO Burns · Flipkart: Month-to-Month Sales Quartile Movement Matrix →