Flash Sale Units Sold Inside the Sale Window — SQL Problem
Every flash sale puts a fixed stock of one product on offer between starts_at and ends_at (both inclusive).
- Difficulty: Medium
- Topics: Joins, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #111
Problem statement
Every flash sale puts a fixed stock of one product on offer between starts_at and ends_at (both inclusive). An order line counts towards a sale when it is for the sale's product and its ordered_at falls inside that window; lines a minute early or late were sold at the normal price and do not count.
Return one row per flash sale with sale_id, units_sold (the sum of quantity of the lines that count, 0 if none) and sale_status: sold out when units sold reach stock_allotted, partial when some but fewer units sold, unsold when none did. Order by sale_id.
Tables
Table: flash_sales
| Column | Type |
|---|---|
| sale_id | int |
| product_id | int |
| starts_at | datetime |
| ends_at | datetime |
| stock_allotted | int |
Primary key: sale_id.
One row per flash sale. A product can have several sales at different times.
Table: order_items
| Column | Type |
|---|---|
| item_id | int |
| product_id | int |
| quantity | int |
| ordered_at | datetime |
Primary key: item_id.
One row per order line.
Examples
Example 1
flash_sales
| sale_id | product_id | starts_at | ends_at | stock_allotted |
|---|---|---|---|---|
| 1 | 501 | 2024-10-03 12:00:00 | 2024-10-03 14:00:00 | 5 |
| 2 | 502 | 2024-10-03 20:00:00 | 2024-10-03 21:00:00 | 10 |
| 3 | 503 | 2024-10-04 09:00:00 | 2024-10-04 10:00:00 | 3 |
| 4 | 501 | 2024-10-05 12:00:00 | 2024-10-05 13:00:00 | 4 |
order_items
| item_id | product_id | quantity | ordered_at |
|---|---|---|---|
| 1 | 501 | 2 | 2024-10-03 12:00:00 |
| 2 | 501 | 3 | 2024-10-03 13:59:59 |
| 3 | 501 | 1 | 2024-10-03 14:00:01 |
| 4 | 502 | 4 | 2024-10-03 20:30:00 |
| 5 | 503 | 1 | 2024-10-04 08:59:00 |
| 6 | 501 | 1 | 2024-10-05 12:45:00 |
| 7 | 502 | 2 | 2024-10-03 21:00:00 |
Output
| sale_id | units_sold | sale_status |
|---|---|---|
| 1 | 5 | sold out |
| 2 | 6 | partial |
| 3 | 0 | unsold |
| 4 | 1 | partial |
How to solve Flash Sale Units Sold Inside the Sale Window
This is a range join: a line belongs to a sale when the products match and ordered_at BETWEEN starts_at AND ends_at. BETWEEN is inclusive on both ends, which is what the statement says — the line at exactly 12:00:00 counts, the one at 14:00:01 does not.
Every sale must appear, so the join is a LEFT JOIN from flash_sales, with the whole match condition in ON. Moving the time test to WHERE would turn it back into an inner join: a sale with no matching line has NULL ordered_at, the test fails, and the sale vanishes instead of showing 0 and unsold. SUM over the padding NULL is NULL, so COALESCE(..., 0) gives the zero.
The status is a CASE over the units, tested in order — sold out first (units reached the stock), then partial, then unsold. Grouping by stock_allotted alongside the key lets the CASE use it under ONLY_FULL_GROUP_BY. The alternative computes units in a correlated subquery per sale. With an index on order_items(product_id, ordered_at) each sale is one range scan.
Reference solution (MySQL)
SELECT s.sale_id,
COALESCE(SUM(i.quantity), 0) AS units_sold,
CASE WHEN COALESCE(SUM(i.quantity), 0) >= s.stock_allotted THEN 'sold out'
WHEN COALESCE(SUM(i.quantity), 0) > 0 THEN 'partial'
ELSE 'unsold' END AS sale_status
FROM flash_sales s
LEFT JOIN order_items i
ON i.product_id = s.product_id AND i.ordered_at BETWEEN s.starts_at AND s.ends_at
GROUP BY s.sale_id, s.stock_allotted
ORDER BY s.sale_idAnother way
SELECT sale_id, units AS units_sold,
CASE WHEN units >= stock_allotted THEN 'sold out' WHEN units = 0 THEN 'unsold' ELSE 'partial' END AS sale_status
FROM (
SELECT s.sale_id, s.stock_allotted,
(SELECT IFNULL(SUM(quantity), 0) FROM order_items i WHERE i.product_id = s.product_id AND i.ordered_at >= s.starts_at AND i.ordered_at <= s.ends_at) AS units
FROM flash_sales s
) t
ORDER BY sale_id← Delivery SLA Breaches by Postal Zone · Monthly GMV Against the Previous Calendar Month →