Longest Consecutive Stock-Out Run per SKU

Hard
392.

Longest Consecutive Stock-Out Run per SKU

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

ColumnType
skuvarchar
stock_datedate
closing_qtyint

Primary key (together): sku, stock_date.

One row per SKU per trading day; closing_qty is NULL where the count was not taken.

Example

The rows must come back in the order shown.

ShelfClosing

skustock_dateclosing_qty
DAI-20022025-06-010
DAI-20022025-06-020
DAI-20022025-06-035
DAI-20022025-06-040
DAI-20022025-06-050
DAI-20022025-06-060
SNK-60012025-06-010
SNK-60012025-06-030
GRO-10012025-06-0212
GRO-10012025-06-03NULL

Output

skurun_daysrun_startrun_end
DAI-200232025-06-042025-06-06
SNK-600112025-06-012025-06-01
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.

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

ColumnType
skuvarchar
stock_datedate
closing_qtyint

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

skustock_dateclosing_qty
DAI-20022025-06-010
DAI-20022025-06-020
DAI-20022025-06-035
DAI-20022025-06-040
DAI-20022025-06-050
DAI-20022025-06-060
SNK-60012025-06-010
SNK-60012025-06-030
GRO-10012025-06-0212
GRO-10012025-06-03NULL

Output

skurun_daysrun_startrun_end
DAI-200232025-06-042025-06-06
SNK-600112025-06-012025-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 sku

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