Amazon: First Day Each Seller Breached the 60-Day Order Defect Rate

Hard
592.

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

ColumnTypeValues
order_idint
seller_idint
ordered_ondate
defectenumnone, 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.

Example

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

SellerOrder

order_idseller_idordered_ondefect
1312025-01-05none
2312025-01-20a_to_z_claim
3312025-02-10none
4312025-02-25none
5312025-03-01none
6312025-03-06none
7322025-01-01chargeback
8322025-01-02none
9322025-01-03none
10322025-01-04none
8 more rows

Output

seller_idfirst_breach_onorders_in_windowdefects_in_window
312025-03-0151
322025-01-0551
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: 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

ColumnType
order_idint
seller_idint
ordered_ondate
defectenum(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_idseller_idordered_ondefect
1312025-01-05none
2312025-01-20a_to_z_claim
3312025-02-10none
4312025-02-25none
5312025-03-01none
6312025-03-06none
7322025-01-01chargeback
8322025-01-02none
9322025-01-03none
10322025-01-04none
11322025-01-05none
12322025-01-06none
13322025-01-06none
14322025-01-06none
15322025-01-06none
16322025-01-06none
17332025-01-10negative_feedback
18332025-01-11chargeback

Output

seller_idfirst_breach_onorders_in_windowdefects_in_window
312025-03-0151
322025-01-0551

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 = 1

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