Flipkart: How Often Each Product's Buy Box Changed Hands

Hard
672.

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

ColumnType
snapshot_datedate
product_idint
seller_idint
priceint
seller_ratingdecimal

Primary key (together): 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.

Example

The rows must come back in the order shown.

OfferSnapshot

snapshot_dateproduct_idseller_idpriceseller_rating
2024-10-01119994.3
2024-10-011210494.5
2024-10-021110494.3
2024-10-021210494.5
2024-10-03129994.5
2024-10-03139994.5
2024-10-05139494.1
2024-10-01244994
2024-10-02244994
2024-10-02214994
1 more row

Output

product_idsnapshot_dayswinner_changes
142
221
310
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: 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

ColumnType
snapshot_datedate
product_idint
seller_idint
priceint
seller_ratingdecimal

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_dateproduct_idseller_idpriceseller_rating
2024-10-01119994.3
2024-10-011210494.5
2024-10-021110494.3
2024-10-021210494.5
2024-10-03129994.5
2024-10-03139994.5
2024-10-05139494.1
2024-10-01244994
2024-10-02244994
2024-10-02214994
2024-10-033219994.2

Output

product_idsnapshot_dayswinner_changes
142
221
310

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_id

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