Flipkart: How Often Each Product's Buy Box Changed Hands
When several sellers offer the same product, one wins the buy box — the default "Buy Now" offer.
- Difficulty: Hard
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #672
Problem statement
When several sellers offer the same product, one wins the buy box — the default "Buy Now" offer. In this exercise the winner on a day is the offer with the lowest price; ties go to the higher seller_rating, then to the lower seller_id. Pricing analysts want to know how unstable the buy box is.
Offers are snapshotted daily, but not every product has offers every day. For each product, take its snapshot days in date order and count how many times the winner differs from the winner on the product's previous snapshot day. Return product_id, snapshot_days (days with at least one offer) and winner_changes, ordered by winner_changes descending, then product_id.
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: OfferSnapshot
| Column | Type |
|---|---|
| snapshot_date | date |
| product_id | int |
| seller_id | int |
| price | int |
| seller_rating | decimal |
Primary key: snapshot_date, product_id, seller_id.
One row per seller's live offer on a product on a day; seller_rating has one decimal place.
Examples
Example 1
OfferSnapshot
| snapshot_date | product_id | seller_id | price | seller_rating |
|---|---|---|---|---|
| 2024-10-01 | 1 | 1 | 999 | 4.3 |
| 2024-10-01 | 1 | 2 | 1049 | 4.5 |
| 2024-10-02 | 1 | 1 | 1049 | 4.3 |
| 2024-10-02 | 1 | 2 | 1049 | 4.5 |
| 2024-10-03 | 1 | 2 | 999 | 4.5 |
| 2024-10-03 | 1 | 3 | 999 | 4.5 |
| 2024-10-05 | 1 | 3 | 949 | 4.1 |
| 2024-10-01 | 2 | 4 | 499 | 4 |
| 2024-10-02 | 2 | 4 | 499 | 4 |
| 2024-10-02 | 2 | 1 | 499 | 4 |
| 2024-10-03 | 3 | 2 | 1999 | 4.2 |
Output
| product_id | snapshot_days | winner_changes |
|---|---|---|
| 1 | 4 | 2 |
| 2 | 2 | 1 |
| 3 | 1 | 0 |
How to solve Flipkart: How Often Each Product's Buy Box Changed Hands
Two window passes do the work. The first picks the winner of each product-day: ROW_NUMBER() partitioned by product and date, ordered by price, then seller_rating DESC, then seller_id. The three-level order is what makes the pick unique — two sellers at the same price and rating would otherwise make the winner, and every count after it, depend on storage order.
The second pass lines up each product's winners by date and fetches the previous one with LAG(seller_id). Because only days with offers have a winner row, "previous" means the product's previous snapshot day, which is exactly what the statement asks — a day with no offers is skipped, not treated as a change. A change is a day whose previous winner exists and differs; the first day has a NULL previous winner and counts as none. Then a GROUP BY per product counts days and changes.
The alternative finds winners with NOT EXISTS (no better offer the same day) and the previous day with a correlated MAX(snapshot_date) < this day. It is quadratic but shows the logic without windows; the window version is two sorts.
Reference solution (MySQL)
WITH ranked AS (
SELECT snapshot_date, product_id, seller_id,
ROW_NUMBER() OVER (PARTITION BY product_id, snapshot_date ORDER BY price, seller_rating DESC, seller_id) AS rn
FROM OfferSnapshot
), winners AS (
SELECT product_id, snapshot_date, seller_id,
LAG(seller_id) OVER (PARTITION BY product_id ORDER BY snapshot_date) AS prev_winner
FROM ranked WHERE rn = 1
)
SELECT product_id,
COUNT(*) AS snapshot_days,
SUM(CASE WHEN prev_winner IS NOT NULL AND prev_winner <> seller_id THEN 1 ELSE 0 END) AS winner_changes
FROM winners
GROUP BY product_id
ORDER BY winner_changes DESC, product_idAnother way
WITH win AS (
SELECT o.snapshot_date, o.product_id, o.seller_id FROM OfferSnapshot o
WHERE NOT EXISTS (
SELECT 1 FROM OfferSnapshot b
WHERE b.snapshot_date = o.snapshot_date AND b.product_id = o.product_id
AND (b.price < o.price
OR (b.price = o.price AND b.seller_rating > o.seller_rating)
OR (b.price = o.price AND b.seller_rating = o.seller_rating AND b.seller_id < o.seller_id)))
)
SELECT w.product_id, COUNT(*) AS snapshot_days,
SUM(CASE WHEN EXISTS (
SELECT 1 FROM win p
WHERE p.product_id = w.product_id AND p.seller_id <> w.seller_id
AND p.snapshot_date = (SELECT MAX(q.snapshot_date) FROM win q WHERE q.product_id = w.product_id AND q.snapshot_date < w.snapshot_date))
THEN 1 ELSE 0 END) AS winner_changes
FROM win w
GROUP BY w.product_id
ORDER BY winner_changes DESC, w.product_id← Flipkart: First Day a Seller Logged Three Returns in Seven Days · Google: Zero-Result Searches per Locale →