Flipkart: Month-to-Month Sales Quartile Movement Matrix
Category managers want to know how stable their rankings are: does a product that sells in the top quarter of its category one month stay there the next, or…
- Difficulty: Hard
- Topics: Window Functions, Joins, Conditional Logic, Dates
- Dialect: MySQL
- Problem: #668
Problem statement
Category managers want to know how stable their rankings are: does a product that sells in the top quarter of its category one month stay there the next, or slide down?
For every category and calendar month, rank the products of that category that sold that month by total units, highest first (ties to the lower product_id), and split them into quartiles 1–4 exactly as NTILE(4) does. A move is a product's quartile in one month paired with its quartile in the next calendar month — only when the product sold in both months (a month without sales means no move into or out of it). Return a movement matrix: for each category and from_quartile with at least one move, category, from_quartile, and to_q1, to_q2, to_q3, to_q4 — how many moves landed in each quartile (0 if none). Order by category, then from_quartile.
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: Product
| Column | Type |
|---|---|
| product_id | int |
| product_name | varchar |
| category | varchar |
Primary key: product_id.
One row per catalogue product.
Table: ProductSale
| Column | Type |
|---|---|
| sale_id | int |
| product_id | int |
| sold_on | date |
| units | int |
Primary key: sale_id.
One row per day's sales of a product (a product can have several rows in a month); product_id always names a product.
Examples
Example 1
Product
| product_id | product_name | category |
|---|---|---|
| 1 | Nova X5 | Mobiles |
| 2 | Pixelon 8a | Mobiles |
| 3 | Redstar 12 | Mobiles |
| 4 | Moto Lite | Mobiles |
| 5 | Galaxy F15 | Mobiles |
| 6 | BassBuds Pro | Audio |
| 7 | Thump Mini | Audio |
ProductSale
| sale_id | product_id | sold_on | units |
|---|---|---|---|
| 1 | 1 | 2024-06-10 | 50 |
| 2 | 2 | 2024-06-11 | 40 |
| 3 | 3 | 2024-06-12 | 30 |
| 4 | 4 | 2024-06-13 | 20 |
| 5 | 5 | 2024-06-14 | 10 |
| 6 | 1 | 2024-07-10 | 30 |
| 7 | 2 | 2024-07-11 | 55 |
| 8 | 3 | 2024-07-12 | 55 |
| 9 | 4 | 2024-07-13 | 10 |
| 10 | 5 | 2024-07-14 | 5 |
| 11 | 2 | 2024-08-05 | 70 |
| 12 | 1 | 2024-08-06 | 60 |
| 13 | 3 | 2024-08-07 | 20 |
| 14 | 4 | 2024-08-08 | 10 |
| 15 | 1 | 2024-09-03 | 40 |
| 16 | 3 | 2024-09-04 | 30 |
| 17 | 4 | 2024-09-05 | 20 |
| 18 | 2 | 2024-10-02 | 50 |
| 19 | 2 | 2024-10-20 | 30 |
| 20 | 1 | 2024-10-21 | 10 |
| 21 | 3 | 2024-10-22 | 5 |
| 22 | 6 | 2024-08-15 | 9 |
| 23 | 7 | 2024-08-16 | 3 |
| 24 | 6 | 2024-09-15 | 4 |
| 25 | 7 | 2024-09-16 | 4 |
| 26 | 6 | 2024-10-15 | 7 |
| 27 | 6 | 2024-12-15 | 1 |
| 28 | 7 | 2024-12-16 | 8 |
Output
| category | from_quartile | to_q1 | to_q2 | to_q3 | to_q4 |
|---|---|---|---|---|---|
| Audio | 1 | 2 | 0 | 0 | 0 |
| Audio | 2 | 0 | 1 | 0 | 0 |
| Mobiles | 1 | 2 | 2 | 1 | 0 |
| Mobiles | 2 | 2 | 1 | 1 | 0 |
| Mobiles | 3 | 0 | 1 | 1 | 1 |
| Mobiles | 4 | 0 | 0 | 1 | 1 |
How to solve Flipkart: Month-to-Month Sales Quartile Movement Matrix
Three steps. Monthly totals: sum the daily rows per product and month, keeping the month as a number YEAR * 12 + MONTH, which steps by exactly one from December to January. Quartiles: NTILE(4) partitioned by category and month splits that month's sellers into four nearly equal buckets, the first ones taking the remainder — with five products the buckets hold 2, 1, 1, 1, with three products only quartiles 1–3 exist. The ORDER BY must be total (units, then product_id) or tied products would land in buckets by chance.
Moves: a self join of the quartiled rows on the same product and b.month_no = a.month_no + 1 pairs each month with the next; a product that skipped a month has no row there, so no move is invented across the gap. Finally a crosstab: group the moves by category and starting quartile and count each destination with SUM(CASE WHEN to_quartile = k THEN 1 ELSE 0 END) — one column per quartile, zeros included. A starting quartile with no moves has no group and no row.
The alternative replaces NTILE with its arithmetic (the first n MOD 4 buckets hold one extra row), and finds the previous month with LAG, keeping a pair only when prev_month = month_no - 1 — because LAG would otherwise pair months across a gap. Each version sorts every category-month once.
Reference solution (MySQL)
WITH monthly AS (
SELECT s.product_id, p.category,
YEAR(s.sold_on) * 12 + MONTH(s.sold_on) AS month_no,
SUM(s.units) AS units
FROM ProductSale s
JOIN Product p ON p.product_id = s.product_id
GROUP BY s.product_id, p.category, YEAR(s.sold_on) * 12 + MONTH(s.sold_on)
), quartiled AS (
SELECT product_id, category, month_no,
NTILE(4) OVER (PARTITION BY category, month_no ORDER BY units DESC, product_id) AS quartile
FROM monthly
), moves AS (
SELECT a.category, a.quartile AS from_quartile, b.quartile AS to_quartile
FROM quartiled a
JOIN quartiled b ON b.product_id = a.product_id AND b.month_no = a.month_no + 1
)
SELECT category, from_quartile,
SUM(CASE WHEN to_quartile = 1 THEN 1 ELSE 0 END) AS to_q1,
SUM(CASE WHEN to_quartile = 2 THEN 1 ELSE 0 END) AS to_q2,
SUM(CASE WHEN to_quartile = 3 THEN 1 ELSE 0 END) AS to_q3,
SUM(CASE WHEN to_quartile = 4 THEN 1 ELSE 0 END) AS to_q4
FROM moves
GROUP BY category, from_quartile
ORDER BY category, from_quartileAnother way
WITH monthly AS (
SELECT s.product_id, p.category, YEAR(s.sold_on) * 12 + MONTH(s.sold_on) AS month_no, SUM(s.units) AS units
FROM ProductSale s JOIN Product p ON p.product_id = s.product_id
GROUP BY s.product_id, p.category, YEAR(s.sold_on) * 12 + MONTH(s.sold_on)
), ranked AS (
SELECT product_id, category, month_no,
ROW_NUMBER() OVER (PARTITION BY category, month_no ORDER BY units DESC, product_id) AS pos,
COUNT(*) OVER (PARTITION BY category, month_no) AS cnt
FROM monthly
), quartiled AS (
SELECT product_id, category, month_no,
CASE WHEN pos <= (cnt MOD 4) * (cnt DIV 4 + 1) THEN FLOOR((pos - 1) / (cnt DIV 4 + 1)) + 1
ELSE cnt MOD 4 + FLOOR((pos - 1 - (cnt MOD 4) * (cnt DIV 4 + 1)) / (cnt DIV 4)) + 1 END AS quartile
FROM ranked
), seq AS (
SELECT category, month_no, quartile,
LAG(quartile) OVER (PARTITION BY product_id ORDER BY month_no) AS prev_quartile,
LAG(month_no) OVER (PARTITION BY product_id ORDER BY month_no) AS prev_month
FROM quartiled
)
SELECT category, prev_quartile AS from_quartile,
COUNT(CASE WHEN quartile = 1 THEN 1 END) AS to_q1,
COUNT(CASE WHEN quartile = 2 THEN 1 END) AS to_q2,
COUNT(CASE WHEN quartile = 3 THEN 1 END) AS to_q3,
COUNT(CASE WHEN quartile = 4 THEN 1 END) AS to_q4
FROM seq
WHERE prev_month = month_no - 1
GROUP BY category, prev_quartile
ORDER BY category, from_quartile← Flipkart: Big Billion Days GMV by Sale Day Against Last Year · Flipkart: Sale-Acquired Buyers Who Came Back for the Next Sale →