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
| Column | Type |
|---|---|
| seller_id | int |
| seller_name | varchar |
| rating | decimal |
Primary key: seller_id.
One row per marketplace seller; rating is the seller's feedback score out of 5.
Table: Offer
| Column | Type |
|---|---|
| offer_id | int |
| asin | varchar |
| seller_id | int |
| price | int |
| shipping | int |
| in_stock | bool |
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_id | seller_name | rating |
|---|---|---|
| 1 | Sunrise Traders | 4.8 |
| 2 | Kaveri Retail | 4.5 |
| 3 | Blue Peak Store | 4.8 |
| 4 | Deccan Deals | 4.1 |
Offer
| offer_id | asin | seller_id | price | shipping | in_stock |
|---|---|---|---|---|---|
| 1 | B0AUDIO001 | 1 | 1499 | 0 | 1 |
| 2 | B0AUDIO001 | 2 | 1399 | 100 | 1 |
| 3 | B0AUDIO001 | 4 | 1299 | 0 | 0 |
| 4 | B0KETTLE02 | 2 | 899 | 40 | 1 |
| 5 | B0KETTLE02 | 3 | 939 | 0 | 1 |
| 6 | B0KETTLE02 | 1 | 939 | 0 | 1 |
| 7 | B0LAMP0003 | 4 | 650 | 0 | 0 |
Output
| asin | seller_name | landed_price |
|---|---|---|
| B0AUDIO001 | Sunrise Traders | 1499 |
| B0KETTLE02 | Sunrise Traders | 939 |
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 = 1Another 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 →