Amazon: Prime Day Deals Not Below the 30-Day Low Price
Before Prime Day, the deals compliance team checks that every deal is a real discount.
- Difficulty: Medium
- Topics: Subqueries, Dates, Joins
- Dialect: MySQL
- Problem: #588
Problem statement
Before Prime Day, the deals compliance team checks that every deal is a real discount. For this exercise the rule is: a deal price must be strictly below the lowest price the product sold at during the 30 days before the deal starts (the 30 calendar days ending the day before starts_on).
Return every deal that breaks the rule — its deal_price is greater than or equal to that 30-day low — with deal_id, asin, deal_price and low_price. A deal whose product has no price recorded in its 30-day window cannot be checked and is not returned. 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: DailyPrice
| Column | Type |
|---|---|
| asin | varchar |
| price_date | date |
| price | int |
Primary key: asin, price_date.
The product's selling price on a day, in rupees; days without a row had no recorded price.
Table: PrimeDayDeal
| Column | Type |
|---|---|
| deal_id | int |
| asin | varchar |
| starts_on | date |
| deal_price | int |
Primary key: deal_id.
One row per submitted deal.
Examples
Example 1
DailyPrice
| asin | price_date | price |
|---|---|---|
| B0HEADSET1 | 2025-06-01 | 1999 |
| B0HEADSET1 | 2025-06-10 | 1699 |
| B0HEADSET1 | 2025-07-01 | 1899 |
| B0BLENDER2 | 2025-06-20 | 3499 |
| B0BLENDER2 | 2025-07-11 | 2999 |
| B0SHOES003 | 2025-05-01 | 2499 |
PrimeDayDeal
| deal_id | asin | starts_on | deal_price |
|---|---|---|---|
| 1 | B0HEADSET1 | 2025-07-11 | 1699 |
| 2 | B0HEADSET1 | 2025-07-01 | 1599 |
| 3 | B0BLENDER2 | 2025-07-11 | 3499 |
| 4 | B0SHOES003 | 2025-07-11 | 1999 |
| 5 | B0HEADSET1 | 2025-07-12 | 1999 |
Output
| deal_id | asin | deal_price | low_price |
|---|---|---|---|
| 3 | B0BLENDER2 | 3499 | 3499 |
| 5 | B0HEADSET1 | 1999 | 1899 |
How to solve Amazon: Prime Day Deals Not Below the 30-Day Low Price
Every deal has its own window, so the low price is a per-deal aggregate. Joining each deal to the price rows of its product whose price_date lies in [starts_on − 30 days, starts_on) and grouping by the deal gives MIN(p.price) as that deal's 30-day low. The comparison with the deal price is a condition on the group, so it goes in HAVING.
The window's ends: DATE_SUB(starts_on, INTERVAL 30 DAY) is included and the start day itself is excluded, which together are exactly the 30 days before the deal. A deal priced at the low is not a discount, so deal_price >= low flags it. A deal with no price rows in its window joins to nothing and so never forms a group — it is skipped, as the statement requires.
The alternative computes the low with a correlated scalar subquery, expressing the window as DATEDIFF(starts_on, price_date) BETWEEN 1 AND 30. When nothing is in the window the subquery is NULL and deal_price >= NULL filters the deal out the same way. With an index on (asin, price_date) each deal reads at most 30 rows either way.
Reference solution (MySQL)
SELECT d.deal_id, d.asin, d.deal_price, MIN(p.price) AS low_price
FROM PrimeDayDeal d
JOIN DailyPrice p
ON p.asin = d.asin
AND p.price_date >= DATE_SUB(d.starts_on, INTERVAL 30 DAY)
AND p.price_date < d.starts_on
GROUP BY d.deal_id, d.asin, d.deal_price
HAVING d.deal_price >= MIN(p.price)Another way
SELECT deal_id, asin, deal_price, low_price FROM (
SELECT d.deal_id, d.asin, d.deal_price,
(SELECT MIN(p.price) FROM DailyPrice p WHERE p.asin = d.asin
AND DATEDIFF(d.starts_on, p.price_date) BETWEEN 1 AND 30) AS low_price
FROM PrimeDayDeal d) t
WHERE deal_price >= low_price← Amazon: Route Stops Delivered Out of the Planned Order · Amazon: Referral and FBA Fees per Seller for October →