Amazon: Buy Box Winner for Each ASIN

Medium
581.

Amazon: Buy Box Winner for Each ASIN

Several marketplace sellers can offer the same product, but the product page shows one offer in the Buy Box. This exercise uses a simplified rule to pick it.

Among the in-stock offers of each ASIN, the winner has the lowest landed price (price + shipping); a tie goes to the seller with the higher rating, and a remaining tie to the lower seller_id. Return asin, seller_name and landed_price for every ASIN with at least one in-stock offer, in any order.

A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.

Tables

Table: Seller

ColumnType
seller_idint
seller_namevarchar
ratingdecimal

Primary key: seller_id.

One row per marketplace seller; rating is the seller's feedback score out of 5.

Table: Offer

ColumnType
offer_idint
asinvarchar
seller_idint
priceint
shippingint
in_stockbool

Primary key: offer_id.

One row per seller's offer on a product, in rupees; a seller has at most one offer per ASIN.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Seller

seller_idseller_namerating
1Sunrise Traders4.8
2Kaveri Retail4.5
3Blue Peak Store4.8
4Deccan Deals4.1

Offer

offer_idasinseller_idpriceshippingin_stock
1B0AUDIO0011149901
2B0AUDIO001213991001
3B0AUDIO0014129900
4B0KETTLE022899401
5B0KETTLE02393901
6B0KETTLE02193901
7B0LAMP0003465000

Output

asinseller_namelanded_price
B0AUDIO001Sunrise Traders1499
B0KETTLE02Sunrise Traders939
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.

Amazon: Buy Box Winner for Each ASIN — SQL Problem

Several marketplace sellers can offer the same product, but the product page shows one offer in the Buy Box. This exercise uses a simplified rule to pick it.

  • Difficulty: Medium
  • Topics: Window Functions, Joins
  • Dialect: MySQL
  • Problem: #581

Problem statement

Several marketplace sellers can offer the same product, but the product page shows one offer in the Buy Box. This exercise uses a simplified rule to pick it.

Among the in-stock offers of each ASIN, the winner has the lowest landed price (price + shipping); a tie goes to the seller with the higher rating, and a remaining tie to the lower seller_id. Return asin, seller_name and landed_price for every ASIN with at least one in-stock offer, in any order.

A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.

Tables

Table: Seller

ColumnType
seller_idint
seller_namevarchar
ratingdecimal

Primary key: seller_id.

One row per marketplace seller; rating is the seller's feedback score out of 5.

Table: Offer

ColumnType
offer_idint
asinvarchar
seller_idint
priceint
shippingint
in_stockbool

Primary key: offer_id.

One row per seller's offer on a product, in rupees; a seller has at most one offer per ASIN.

Examples

Example 1

Seller

seller_idseller_namerating
1Sunrise Traders4.8
2Kaveri Retail4.5
3Blue Peak Store4.8
4Deccan Deals4.1

Offer

offer_idasinseller_idpriceshippingin_stock
1B0AUDIO0011149901
2B0AUDIO001213991001
3B0AUDIO0014129900
4B0KETTLE022899401
5B0KETTLE02393901
6B0KETTLE02193901
7B0LAMP0003465000

Output

asinseller_namelanded_price
B0AUDIO001Sunrise Traders1499
B0KETTLE02Sunrise Traders939

How to solve Amazon: Buy Box Winner for Each ASIN

The winner is the first offer of each ASIN under a three-level ordering, which is exactly what ROW_NUMBER() OVER (PARTITION BY asin ORDER BY landed price, rating DESC, seller_id) computes. The rating lives on Seller, so it is joined in first; out-of-stock offers are removed in WHERE so they never take position 1 — an ASIN whose offers are all out of stock then has no rows and is left out.

The last tie-break on seller_id matters: two sellers with the same landed price and the same rating would otherwise make the winner depend on storage order. Because a seller has at most one offer per ASIN, seller_id is unique within the partition and the order is total, so ROW_NUMBER picks one offer deterministically. RANK would not — it would return both tied offers.

The alternative says the same thing without windows: keep an offer when no other in-stock offer of the same ASIN beats it under the three rules, written as a NOT EXISTS with the comparison spelled out level by level. It is easy to get wrong (each later level must repeat the equalities of the earlier ones), which is why the window form is preferred. Both are linear-ish with an index on (asin).

Reference solution (MySQL)

WITH ranked AS (
  SELECT o.asin, s.seller_name, o.price + o.shipping AS landed_price,
         ROW_NUMBER() OVER (PARTITION BY o.asin
                            ORDER BY o.price + o.shipping, s.rating DESC, s.seller_id) AS pos
  FROM Offer o
  JOIN Seller s ON s.seller_id = o.seller_id
  WHERE o.in_stock = 1
)
SELECT asin, seller_name, landed_price FROM ranked WHERE pos = 1

Another way

SELECT o.asin, s.seller_name, o.price + o.shipping AS landed_price
FROM Offer o JOIN Seller s ON s.seller_id = o.seller_id
WHERE o.in_stock = 1 AND NOT EXISTS (
  SELECT 1 FROM Offer o2 JOIN Seller s2 ON s2.seller_id = o2.seller_id
  WHERE o2.asin = o.asin AND o2.in_stock = 1 AND (
    o2.price + o2.shipping < o.price + o.shipping
    OR (o2.price + o2.shipping = o.price + o.shipping AND s2.rating > s.rating)
    OR (o2.price + o2.shipping = o.price + o.shipping AND s2.rating = s.rating AND s2.seller_id < s.seller_id)))

← Amazon: EC2 Billable Seconds With the One-Minute Minimum · Amazon: Star Rating Breakdown for Each Product →