Amazon: Lightning Deals That Sold Out Before Ending
The Prime Day deals team wants to know which lightning deals were under-stocked: the ones that sold out before their window closed, and how much selling…
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #573
Problem statement
The Prime Day deals team wants to know which lightning deals were under-stocked: the ones that sold out before their window closed, and how much selling time was left on the clock.
A deal sold out when units_claimed reached units_offered. Return every deal that sold out with its last claim strictly before ends_at — a deal whose last unit went at the exact closing second is not early. Return deal_id, asin and minutes_left, the whole minutes from last_claim_at to ends_at (partial minutes dropped, as TIMESTAMPDIFF does). 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: LightningDeal
| Column | Type |
|---|---|
| deal_id | int |
| asin | varchar |
| starts_at | datetime |
| ends_at | datetime |
| units_offered | int |
| units_claimed | int |
| last_claim_at | datetime |
Primary key: deal_id.
One row per lightning deal. units_claimed never exceeds units_offered; last_claim_at is the time of the latest claim, NULL when nothing was claimed.
Examples
Example 1
LightningDeal
| deal_id | asin | starts_at | ends_at | units_offered | units_claimed | last_claim_at |
|---|---|---|---|---|---|---|
| 1 | B0C1XK7Q2M | 2025-07-08 08:00:00 | 2025-07-08 14:00:00 | 500 | 500 | 2025-07-08 11:42:30 |
| 2 | B09V3HN1KD | 2025-07-08 09:00:00 | 2025-07-08 15:00:00 | 300 | 287 | 2025-07-08 14:58:00 |
| 3 | B0BSHF7WHW | 2025-07-08 10:00:00 | 2025-07-08 16:00:00 | 200 | 200 | 2025-07-08 16:00:00 |
| 4 | B0D5HQ2LJ9 | 2025-07-08 12:00:00 | 2025-07-08 18:00:00 | 150 | 0 | NULL |
| 5 | B0CHX1W2Y8 | 2025-07-08 12:00:00 | 2025-07-08 18:00:00 | 1000 | 1000 | 2025-07-08 17:59:20 |
| 6 | B0C1XK7Q2M | 2025-07-09 08:00:00 | 2025-07-09 20:00:00 | 400 | 400 | 2025-07-09 09:15:00 |
Output
| deal_id | asin | minutes_left |
|---|---|---|
| 1 | B0C1XK7Q2M | 137 |
| 5 | B0CHX1W2Y8 | 0 |
| 6 | B0C1XK7Q2M | 645 |
How to solve Amazon: Lightning Deals That Sold Out Before Ending
This is a filter on one table plus one computed column. A deal sold out when units_claimed = units_offered; because claims never exceed the offer, >= says the same thing. A deal that sold out has at least one claim, so its last_claim_at is never NULL — and for deals that did not sell out the time is irrelevant because the first condition already drops them.
The second condition, last_claim_at < ends_at, is strict on purpose: a deal whose last unit went at the closing second used its whole window and was not under-stocked. Datetimes stored as YYYY-MM-DD HH:MM:SS compare correctly.
TIMESTAMPDIFF(MINUTE, last_claim_at, ends_at) returns the whole minutes between the two times, truncating any leftover seconds — a deal that sold out 40 seconds before the end shows 0 minutes but is still early. The alternative counts seconds and divides with DIV 60, which truncates the same way, and states the "before" test as a positive second count. Either is one scan of the table.
Reference solution (MySQL)
SELECT deal_id, asin, TIMESTAMPDIFF(MINUTE, last_claim_at, ends_at) AS minutes_left
FROM LightningDeal
WHERE units_claimed = units_offered
AND last_claim_at < ends_atAnother way
SELECT deal_id, asin, TIMESTAMPDIFF(SECOND, last_claim_at, ends_at) DIV 60 AS minutes_left FROM LightningDeal WHERE units_claimed >= units_offered AND TIMESTAMPDIFF(SECOND, last_claim_at, ends_at) > 0← Airbnb: Attribute Each Booking to Its Last Search · Amazon: Search Queries Mentioning Each Brand →