Amazon: Prime Day Deals Not Below the 30-Day Low Price

Medium
588.

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

ColumnType
asinvarchar
price_datedate
priceint

Primary key (together): asin, price_date.

The product's selling price on a day, in rupees; days without a row had no recorded price.

Table: PrimeDayDeal

ColumnType
deal_idint
asinvarchar
starts_ondate
deal_priceint

Primary key: deal_id.

One row per submitted deal.

Example

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

DailyPrice

asinprice_dateprice
B0HEADSET12025-06-011999
B0HEADSET12025-06-101699
B0HEADSET12025-07-011899
B0BLENDER22025-06-203499
B0BLENDER22025-07-112999
B0SHOES0032025-05-012499

PrimeDayDeal

deal_idasinstarts_ondeal_price
1B0HEADSET12025-07-111699
2B0HEADSET12025-07-011599
3B0BLENDER22025-07-113499
4B0SHOES0032025-07-111999
5B0HEADSET12025-07-121999

Output

deal_idasindeal_pricelow_price
3B0BLENDER234993499
5B0HEADSET119991899
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: 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

ColumnType
asinvarchar
price_datedate
priceint

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

ColumnType
deal_idint
asinvarchar
starts_ondate
deal_priceint

Primary key: deal_id.

One row per submitted deal.

Examples

Example 1

DailyPrice

asinprice_dateprice
B0HEADSET12025-06-011999
B0HEADSET12025-06-101699
B0HEADSET12025-07-011899
B0BLENDER22025-06-203499
B0BLENDER22025-07-112999
B0SHOES0032025-05-012499

PrimeDayDeal

deal_idasinstarts_ondeal_price
1B0HEADSET12025-07-111699
2B0HEADSET12025-07-011599
3B0BLENDER22025-07-113499
4B0SHOES0032025-07-111999
5B0HEADSET12025-07-121999

Output

deal_idasindeal_pricelow_price
3B0BLENDER234993499
5B0HEADSET119991899

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 →