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
| Column | Type |
|---|---|
| sale_year | int |
| starts_on | date |
| ends_on | date |
Primary key: sale_year.
Exactly two rows, for 2023 and 2024. Both dates are inclusive; a sale lasts 1 to 12 days.
Table: Category
| Column | Type |
|---|---|
| category | varchar |
Primary key: category.
Every category on the war-room board, whether or not it sold anything.
Table: SaleItem
| Column | Type |
|---|---|
| item_id | int |
| category | varchar |
| sold_on | date |
| gmv | int |
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_year | starts_on | ends_on |
|---|---|---|
| 2023 | 2023-10-08 | 2023-10-11 |
| 2024 | 2024-09-27 | 2024-10-01 |
Category
| category |
|---|
| Books |
| Fashion |
| Mobiles |
SaleItem
| item_id | category | sold_on | gmv |
|---|---|---|---|
| 1 | Mobiles | 2023-10-08 | 1000 |
| 2 | Mobiles | 2023-10-08 | 500 |
| 3 | Mobiles | 2023-10-10 | 800 |
| 4 | Mobiles | 2024-09-27 | 2000 |
| 5 | Mobiles | 2024-09-29 | 700 |
| 6 | Mobiles | 2024-10-01 | 300 |
| 7 | Mobiles | 2024-10-02 | 999 |
| 8 | Fashion | 2023-10-11 | 400 |
| 9 | Fashion | 2024-09-28 | 650 |
| 10 | Fashion | 2023-10-07 | 100 |
Output
| category | day_no | gmv_2023 | gmv_2024 |
|---|---|---|---|
| Books | 1 | 0 | 0 |
| Books | 2 | 0 | 0 |
| Books | 3 | 0 | 0 |
| Books | 4 | 0 | 0 |
| Books | 5 | NULL | 0 |
| Fashion | 1 | 0 | 0 |
| Fashion | 2 | 0 | 650 |
| Fashion | 3 | 0 | 0 |
| Fashion | 4 | 400 | 0 |
| Fashion | 5 | NULL | 0 |
| Mobiles | 1 | 1500 | 2000 |
| Mobiles | 2 | 0 | 0 |
| Mobiles | 3 | 800 | 700 |
| Mobiles | 4 | 0 | 0 |
| Mobiles | 5 | NULL | 300 |
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_noAnother 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 →