Amazon: First Day Each Seller Breached the 60-Day Order Defect Rate
Seller account health tracks an order defect rate: the share of a seller's orders that drew a defect (negative feedback, a guarantee claim or a chargeback)…
- Difficulty: Hard
- Topics: Joins, Dates, Window Functions
- Dialect: MySQL
- Problem: #592
Problem statement
Seller account health tracks an order defect rate: the share of a seller's orders that drew a defect (negative feedback, a guarantee claim or a chargeback) over a rolling window. For this exercise, a seller breaches on a day d when the orders placed in the 60 days ending on d (from d minus 59 days to d, both inclusive) number at least 5 and more than 10% of them have a defect. Only days on which the seller received at least one order are checked.
For every seller that ever breaches, return seller_id, first_breach_on (the earliest such day), and orders_in_window and defects_in_window for that day's window. Sellers that never breach are left out. Return the rows 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: SellerOrder
| Column | Type |
|---|---|
| order_id | int |
| seller_id | int |
| ordered_on | date |
| defect | enum(none, negative_feedback, a_to_z_claim, chargeback) |
Primary key: order_id.
One row per order a seller received; defect is none for a clean order and otherwise names the defect it drew.
Examples
Example 1
SellerOrder
| order_id | seller_id | ordered_on | defect |
|---|---|---|---|
| 1 | 31 | 2025-01-05 | none |
| 2 | 31 | 2025-01-20 | a_to_z_claim |
| 3 | 31 | 2025-02-10 | none |
| 4 | 31 | 2025-02-25 | none |
| 5 | 31 | 2025-03-01 | none |
| 6 | 31 | 2025-03-06 | none |
| 7 | 32 | 2025-01-01 | chargeback |
| 8 | 32 | 2025-01-02 | none |
| 9 | 32 | 2025-01-03 | none |
| 10 | 32 | 2025-01-04 | none |
| 11 | 32 | 2025-01-05 | none |
| 12 | 32 | 2025-01-06 | none |
| 13 | 32 | 2025-01-06 | none |
| 14 | 32 | 2025-01-06 | none |
| 15 | 32 | 2025-01-06 | none |
| 16 | 32 | 2025-01-06 | none |
| 17 | 33 | 2025-01-10 | negative_feedback |
| 18 | 33 | 2025-01-11 | chargeback |
Output
| seller_id | first_breach_on | orders_in_window | defects_in_window |
|---|---|---|---|
| 31 | 2025-03-01 | 5 | 1 |
| 32 | 2025-01-05 | 5 | 1 |
How to solve Amazon: First Day Each Seller Breached the 60-Day Order Defect Rate
A rolling window over dates is a self join with a date range: take each day the seller had an order (the distinct (seller_id, ordered_on) pairs) and join it to every order of the same seller placed between d − 59 days and d. Grouping by (seller, d) then gives that day's window — COUNT(*) orders and a conditional SUM of the defective ones. A window function with a RANGE frame would need a numeric ordering key; the join works directly on dates and handles days with several orders and long gaps naturally.
The breach rule is applied to those windows: at least 5 orders, and defects strictly above 10%, written defects * 10 > orders to stay in integers (exactly 10% is not a breach). The earliest breaching day per seller is picked with ROW_NUMBER() ordered by date; since each (seller, d) appears once, there are no ties. Sellers with no breaching day have no rows in breach and drop out, and the window's counts are reported for the day found.
The alternative counts each window with two correlated subqueries using DATEDIFF(d, ordered_on) BETWEEN 0 AND 59, tests the rate by division, and keeps the day equal to the seller's MIN breaching day. Both cost about (days × orders per window); with an index on (seller_id, ordered_on) each window is a range read.
Reference solution (MySQL)
WITH days AS (
SELECT DISTINCT seller_id, ordered_on AS d FROM SellerOrder
), win AS (
SELECT x.seller_id, x.d,
COUNT(*) AS orders_in_window,
SUM(CASE WHEN o.defect <> 'none' THEN 1 ELSE 0 END) AS defects_in_window
FROM days x
JOIN SellerOrder o
ON o.seller_id = x.seller_id
AND o.ordered_on BETWEEN DATE_SUB(x.d, INTERVAL 59 DAY) AND x.d
GROUP BY x.seller_id, x.d
), breach AS (
SELECT seller_id, d, orders_in_window, defects_in_window,
ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY d) AS rn
FROM win
WHERE orders_in_window >= 5 AND defects_in_window * 10 > orders_in_window
)
SELECT seller_id, d AS first_breach_on, orders_in_window, defects_in_window
FROM breach
WHERE rn = 1Another way
WITH win AS (
SELECT x.seller_id, x.ordered_on AS d,
(SELECT COUNT(*) FROM SellerOrder o WHERE o.seller_id = x.seller_id AND DATEDIFF(x.ordered_on, o.ordered_on) BETWEEN 0 AND 59) AS orders_in_window,
(SELECT COUNT(*) FROM SellerOrder o WHERE o.seller_id = x.seller_id AND o.defect <> 'none' AND DATEDIFF(x.ordered_on, o.ordered_on) BETWEEN 0 AND 59) AS defects_in_window
FROM (SELECT DISTINCT seller_id, ordered_on FROM SellerOrder) x
), breach AS (
SELECT * FROM win WHERE orders_in_window >= 5 AND defects_in_window / orders_in_window > 0.1
)
SELECT b.seller_id, b.d AS first_breach_on, b.orders_in_window, b.defects_in_window
FROM breach b
WHERE b.d = (SELECT MIN(b2.d) FROM breach b2 WHERE b2.seller_id = b.seller_id)← Amazon: Clearing Price of Each Sponsored Products Auction · Amazon: Package Dwell Time at Each Facility Visit →