Amazon: Lightning Deals That Sold Out Before Ending

Easy
573.

Amazon: Lightning Deals That Sold Out Before Ending

EasyBasicsDates

MySQL

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

ColumnType
deal_idint
asinvarchar
starts_atdatetime
ends_atdatetime
units_offeredint
units_claimedint
last_claim_atdatetime

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.

Example

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

LightningDeal

deal_idasinstarts_atends_atunits_offeredunits_claimedlast_claim_at
1B0C1XK7Q2M2025-07-08 08:00:002025-07-08 14:00:005005002025-07-08 11:42:30
2B09V3HN1KD2025-07-08 09:00:002025-07-08 15:00:003002872025-07-08 14:58:00
3B0BSHF7WHW2025-07-08 10:00:002025-07-08 16:00:002002002025-07-08 16:00:00
4B0D5HQ2LJ92025-07-08 12:00:002025-07-08 18:00:001500NULL
5B0CHX1W2Y82025-07-08 12:00:002025-07-08 18:00:00100010002025-07-08 17:59:20
6B0C1XK7Q2M2025-07-09 08:00:002025-07-09 20:00:004004002025-07-09 09:15:00

Output

deal_idasinminutes_left
1B0C1XK7Q2M137
5B0CHX1W2Y80
6B0C1XK7Q2M645
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: 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

ColumnType
deal_idint
asinvarchar
starts_atdatetime
ends_atdatetime
units_offeredint
units_claimedint
last_claim_atdatetime

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_idasinstarts_atends_atunits_offeredunits_claimedlast_claim_at
1B0C1XK7Q2M2025-07-08 08:00:002025-07-08 14:00:005005002025-07-08 11:42:30
2B09V3HN1KD2025-07-08 09:00:002025-07-08 15:00:003002872025-07-08 14:58:00
3B0BSHF7WHW2025-07-08 10:00:002025-07-08 16:00:002002002025-07-08 16:00:00
4B0D5HQ2LJ92025-07-08 12:00:002025-07-08 18:00:001500NULL
5B0CHX1W2Y82025-07-08 12:00:002025-07-08 18:00:00100010002025-07-08 17:59:20
6B0C1XK7Q2M2025-07-09 08:00:002025-07-09 20:00:004004002025-07-09 09:15:00

Output

deal_idasinminutes_left
1B0C1XK7Q2M137
5B0CHX1W2Y80
6B0C1XK7Q2M645

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_at

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