Overlapping Flash Sales on the Same Product

Hard
122.

Overlapping Flash Sales on the Same Product

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

ColumnType
sale_idint
product_idint
starts_atdatetime
ends_atdatetime
sale_priceint

Primary key: sale_id.

One row per scheduled flash sale; times are on whole minutes and starts_at < ends_at.

Example

The rows must come back in the order shown.

flash_sales

sale_idproduct_idstarts_atends_atsale_price
15012024-11-11 10:00:002024-11-11 12:00:0014999
25012024-11-11 11:30:002024-11-11 13:00:0015499
35012024-11-11 13:00:002024-11-11 14:00:0014499
45012024-11-11 09:00:002024-11-11 15:00:0015999
55022024-11-11 10:00:002024-11-11 11:00:00999
65032024-11-11 10:00:002024-11-11 11:00:001299
75022024-11-11 11:01:002024-11-11 12:00:00949

Output

product_idsale_asale_boverlap_minutes
5011230
50114120
501230
5012490
5013460
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.

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

ColumnType
sale_idint
product_idint
starts_atdatetime
ends_atdatetime
sale_priceint

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_idproduct_idstarts_atends_atsale_price
15012024-11-11 10:00:002024-11-11 12:00:0014999
25012024-11-11 11:30:002024-11-11 13:00:0015499
35012024-11-11 13:00:002024-11-11 14:00:0014499
45012024-11-11 09:00:002024-11-11 15:00:0015999
55022024-11-11 10:00:002024-11-11 11:00:00999
65032024-11-11 10:00:002024-11-11 11:00:001299
75022024-11-11 11:01:002024-11-11 12:00:00949

Output

product_idsale_asale_boverlap_minutes
5011230
50114120
501230
5012490
5013460

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_b

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