Flash Sale Units Sold Inside the Sale Window

Medium
111.

Flash Sale Units Sold Inside the Sale Window

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

ColumnType
sale_idint
product_idint
starts_atdatetime
ends_atdatetime
stock_allottedint

Primary key: sale_id.

One row per flash sale. A product can have several sales at different times.

Table: order_items

ColumnType
item_idint
product_idint
quantityint
ordered_atdatetime

Primary key: item_id.

One row per order line.

Example

The rows must come back in the order shown.

flash_sales

sale_idproduct_idstarts_atends_atstock_allotted
15012024-10-03 12:00:002024-10-03 14:00:005
25022024-10-03 20:00:002024-10-03 21:00:0010
35032024-10-04 09:00:002024-10-04 10:00:003
45012024-10-05 12:00:002024-10-05 13:00:004

order_items

item_idproduct_idquantityordered_at
150122024-10-03 12:00:00
250132024-10-03 13:59:59
350112024-10-03 14:00:01
450242024-10-03 20:30:00
550312024-10-04 08:59:00
650112024-10-05 12:45:00
750222024-10-03 21:00:00

Output

sale_idunits_soldsale_status
15sold out
26partial
30unsold
41partial
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.

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

ColumnType
sale_idint
product_idint
starts_atdatetime
ends_atdatetime
stock_allottedint

Primary key: sale_id.

One row per flash sale. A product can have several sales at different times.

Table: order_items

ColumnType
item_idint
product_idint
quantityint
ordered_atdatetime

Primary key: item_id.

One row per order line.

Examples

Example 1

flash_sales

sale_idproduct_idstarts_atends_atstock_allotted
15012024-10-03 12:00:002024-10-03 14:00:005
25022024-10-03 20:00:002024-10-03 21:00:0010
35032024-10-04 09:00:002024-10-04 10:00:003
45012024-10-05 12:00:002024-10-05 13:00:004

order_items

item_idproduct_idquantityordered_at
150122024-10-03 12:00:00
250132024-10-03 13:59:59
350112024-10-03 14:00:01
450242024-10-03 20:30:00
550312024-10-04 08:59:00
650112024-10-05 12:45:00
750222024-10-03 21:00:00

Output

sale_idunits_soldsale_status
15sold out
26partial
30unsold
41partial

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_id

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