Overlapping Flash Sales on the Same Product — SQL Problem
Two flash sales of the same product running at once split the traffic and confuse the price shown, so the campaign planner flags them.
- Difficulty: Hard
- Topics: Joins, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #122
Problem statement
Two flash sales of the same product running at once split the traffic and confuse the price shown, so the campaign planner flags them. A sale runs from starts_at to ends_at, both inclusive, so a sale ending at 14:00:00 overlaps one starting at 14:00:00 (an overlap of 0 minutes).
Return every pair of overlapping sales of the same product, once per pair, with the columns product_id, sale_a (the smaller sale_id), sale_b and overlap_minutes — the minutes from the later start to the earlier end. Order by product_id, sale_a, sale_b.
Tables
Table: flash_sales
| Column | Type |
|---|---|
| sale_id | int |
| product_id | int |
| starts_at | datetime |
| ends_at | datetime |
| sale_price | int |
Primary key: sale_id.
One row per scheduled flash sale; times are on whole minutes and starts_at < ends_at.
Examples
Example 1
flash_sales
| sale_id | product_id | starts_at | ends_at | sale_price |
|---|---|---|---|---|
| 1 | 501 | 2024-11-11 10:00:00 | 2024-11-11 12:00:00 | 14999 |
| 2 | 501 | 2024-11-11 11:30:00 | 2024-11-11 13:00:00 | 15499 |
| 3 | 501 | 2024-11-11 13:00:00 | 2024-11-11 14:00:00 | 14499 |
| 4 | 501 | 2024-11-11 09:00:00 | 2024-11-11 15:00:00 | 15999 |
| 5 | 502 | 2024-11-11 10:00:00 | 2024-11-11 11:00:00 | 999 |
| 6 | 503 | 2024-11-11 10:00:00 | 2024-11-11 11:00:00 | 1299 |
| 7 | 502 | 2024-11-11 11:01:00 | 2024-11-11 12:00:00 | 949 |
Output
| product_id | sale_a | sale_b | overlap_minutes |
|---|---|---|---|
| 501 | 1 | 2 | 30 |
| 501 | 1 | 4 | 120 |
| 501 | 2 | 3 | 0 |
| 501 | 2 | 4 | 90 |
| 501 | 3 | 4 | 60 |
How to solve Overlapping Flash Sales on the Same Product
Finding pairs of rows means a self join: two copies of flash_sales matched on product_id. The condition a.sale_id < b.sale_id does two jobs at once — it drops a sale paired with itself and keeps only one of the two orderings of every pair, so the smaller id lands in sale_a as asked.
The overlap test is the classic one for intervals: a.starts_at <= b.ends_at AND b.starts_at <= a.ends_at. It covers every arrangement — partial overlap (sales 1 and 2), one sale inside another (sale 4 contains 1, 2 and 3), and touching ends (sales 2 and 3 meet at 13:00 and overlap for 0 minutes because the bounds are inclusive). Sales 5 and 7 are a minute apart and do not overlap. Testing only "b starts inside a" misses the containment case where b starts first.
The shared stretch runs from the later start to the earlier end, GREATEST(starts) to LEAST(ends), and TIMESTAMPDIFF(MINUTE, …) measures it — exact here because the times are on whole minutes. The alternative computes the two bounds with CASE and tests later_start <= earlier_end, which is the same overlap condition stated on the bounds. The self join is quadratic per product; with an index on (product_id, starts_at) it stays small.
Reference solution (MySQL)
SELECT a.product_id, a.sale_id AS sale_a, b.sale_id AS sale_b,
TIMESTAMPDIFF(MINUTE, GREATEST(a.starts_at, b.starts_at), LEAST(a.ends_at, b.ends_at)) AS overlap_minutes
FROM flash_sales a
JOIN flash_sales b
ON b.product_id = a.product_id
AND a.sale_id < b.sale_id
AND a.starts_at <= b.ends_at
AND b.starts_at <= a.ends_at
ORDER BY a.product_id, sale_a, sale_bAnother way
SELECT product_id, sale_a, sale_b, TIMESTAMPDIFF(MINUTE, later_start, earlier_end) AS overlap_minutes FROM (
SELECT a.product_id, a.sale_id AS sale_a, b.sale_id AS sale_b,
CASE WHEN a.starts_at >= b.starts_at THEN a.starts_at ELSE b.starts_at END AS later_start,
CASE WHEN a.ends_at <= b.ends_at THEN a.ends_at ELSE b.ends_at END AS earlier_end
FROM flash_sales a, flash_sales b
WHERE a.product_id = b.product_id AND a.sale_id < b.sale_id
) t
WHERE later_start <= earlier_end
ORDER BY product_id, sale_a, sale_b← The Redemption That Exhausted Each Coupon Budget · Students Short of 75% Attendance in a Course →