Longest Consecutive Stock-Out Run per SKU — SQL Problem
A flagship store logs each SKU's closing stock per day. A stock-out run is a sequence of consecutive calendar days on which the SKU closed at 0.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #392
Problem statement
A flagship store logs each SKU's closing stock per day. A stock-out run is a sequence of consecutive calendar days on which the SKU closed at 0. A day that closed above 0, a day with no closing recorded (closing_qty NULL), or a day missing from the log (the store was shut) ends the run.
For every SKU that had at least one stock-out day, return its longest run: sku, run_days, run_start and run_end. If two runs are equally long, report the earlier one. Order the rows by sku.
Tables
Table: ShelfClosing
| Column | Type |
|---|---|
| sku | varchar |
| stock_date | date |
| closing_qty | int |
Primary key: sku, stock_date.
One row per SKU per trading day; closing_qty is NULL where the count was not taken.
Examples
Example 1
ShelfClosing
| sku | stock_date | closing_qty |
|---|---|---|
| DAI-2002 | 2025-06-01 | 0 |
| DAI-2002 | 2025-06-02 | 0 |
| DAI-2002 | 2025-06-03 | 5 |
| DAI-2002 | 2025-06-04 | 0 |
| DAI-2002 | 2025-06-05 | 0 |
| DAI-2002 | 2025-06-06 | 0 |
| SNK-6001 | 2025-06-01 | 0 |
| SNK-6001 | 2025-06-03 | 0 |
| GRO-1001 | 2025-06-02 | 12 |
| GRO-1001 | 2025-06-03 | NULL |
Output
| sku | run_days | run_start | run_end |
|---|---|---|---|
| DAI-2002 | 3 | 2025-06-04 | 2025-06-06 |
| SNK-6001 | 1 | 2025-06-01 | 2025-06-01 |
How to solve Longest Consecutive Stock-Out Run per SKU
This is gaps and islands. Filter the log to stock-out days only. Within one SKU, number those days in date order with ROW_NUMBER(). For consecutive dates the day count (DATEDIFF(stock_date, <any fixed date>)) and the row number both go up by one, so their difference is constant; as soon as a day is missing — because it closed above zero, was not counted, or is absent from the log — the day count jumps and the difference changes. That difference is the island key.
Grouping by (sku, island) gives each run's first day, last day and length. Choosing the longest per SKU is a top-1-per-group problem with an explicit tie rule, so ROW_NUMBER() OVER (PARTITION BY sku ORDER BY run_days DESC, run_start) picks exactly one run, the earlier of equal ones.
The alternative marks the start of each run with LAG (a day is a start unless the previous stock-out day is exactly one day earlier), numbers runs with a running sum of those flags, and keeps the run no other run beats with NOT EXISTS. Both are a couple of sorts per SKU. NULL closings drop out of the filter, so they break a run just like a missing day.
Reference solution (MySQL)
WITH zero_days AS (
SELECT sku, stock_date,
DATEDIFF(stock_date, '2025-01-01')
- ROW_NUMBER() OVER (PARTITION BY sku ORDER BY stock_date) AS island
FROM ShelfClosing
WHERE closing_qty = 0
), runs AS (
SELECT sku, MIN(stock_date) AS run_start, MAX(stock_date) AS run_end, COUNT(*) AS run_days
FROM zero_days
GROUP BY sku, island
), ranked AS (
SELECT sku, run_days, run_start, run_end,
ROW_NUMBER() OVER (PARTITION BY sku ORDER BY run_days DESC, run_start) AS rn
FROM runs
)
SELECT sku, run_days, run_start, run_end
FROM ranked
WHERE rn = 1
ORDER BY skuAnother way
WITH z AS (
SELECT sku, stock_date,
CASE WHEN DATEDIFF(stock_date, LAG(stock_date) OVER (PARTITION BY sku ORDER BY stock_date)) = 1 THEN 0 ELSE 1 END AS starts_run
FROM ShelfClosing WHERE closing_qty = 0
), g AS (
SELECT sku, stock_date, SUM(starts_run) OVER (PARTITION BY sku ORDER BY stock_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_no FROM z
), runs AS (
SELECT sku, run_no, COUNT(*) AS run_days, MIN(stock_date) AS run_start, MAX(stock_date) AS run_end FROM g GROUP BY sku, run_no
)
SELECT r.sku, r.run_days, r.run_start, r.run_end FROM runs r
WHERE NOT EXISTS (SELECT 1 FROM runs o WHERE o.sku = r.sku AND (o.run_days > r.run_days OR (o.run_days = r.run_days AND o.run_start < r.run_start)))
ORDER BY r.sku← FIFO Valuation of the Stock Left on Hand · First Day a SKU's Stock Falls Below Its Reorder Point →