Flipkart: Month-to-Month Sales Quartile Movement Matrix

Hard
668.

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

ColumnType
product_idint
product_namevarchar
categoryvarchar

Primary key: product_id.

One row per catalogue product.

Table: ProductSale

ColumnType
sale_idint
product_idint
sold_ondate
unitsint

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.

Example

The rows must come back in the order shown.

Product

product_idproduct_namecategory
1Nova X5Mobiles
2Pixelon 8aMobiles
3Redstar 12Mobiles
4Moto LiteMobiles
5Galaxy F15Mobiles
6BassBuds ProAudio
7Thump MiniAudio

ProductSale

sale_idproduct_idsold_onunits
112024-06-1050
222024-06-1140
332024-06-1230
442024-06-1320
552024-06-1410
612024-07-1030
722024-07-1155
832024-07-1255
942024-07-1310
1052024-07-145
18 more rows

Output

categoryfrom_quartileto_q1to_q2to_q3to_q4
Audio12000
Audio20100
Mobiles12210
Mobiles22110
Mobiles30111
Mobiles40011
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: 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

ColumnType
product_idint
product_namevarchar
categoryvarchar

Primary key: product_id.

One row per catalogue product.

Table: ProductSale

ColumnType
sale_idint
product_idint
sold_ondate
unitsint

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_idproduct_namecategory
1Nova X5Mobiles
2Pixelon 8aMobiles
3Redstar 12Mobiles
4Moto LiteMobiles
5Galaxy F15Mobiles
6BassBuds ProAudio
7Thump MiniAudio

ProductSale

sale_idproduct_idsold_onunits
112024-06-1050
222024-06-1140
332024-06-1230
442024-06-1320
552024-06-1410
612024-07-1030
722024-07-1155
832024-07-1255
942024-07-1310
1052024-07-145
1122024-08-0570
1212024-08-0660
1332024-08-0720
1442024-08-0810
1512024-09-0340
1632024-09-0430
1742024-09-0520
1822024-10-0250
1922024-10-2030
2012024-10-2110
2132024-10-225
2262024-08-159
2372024-08-163
2462024-09-154
2572024-09-164
2662024-10-157
2762024-12-151
2872024-12-168

Output

categoryfrom_quartileto_q1to_q2to_q3to_q4
Audio12000
Audio20100
Mobiles12210
Mobiles22110
Mobiles30111
Mobiles40011

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_quartile

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