First Day a SKU's Stock Falls Below Its Reorder Point

Hard
393.

First Day a SKU's Stock Falls Below Its Reorder Point

Planning starts each SKU at an opening balance and applies its daily net movement (receipts minus issues, so it can be negative). The closing balance on a movement day is the opening balance plus every net movement up to and including that day.

For every SKU whose closing balance ever goes strictly below its reorder point on a movement day, return the first such day: sku, breach_date, closing_balance (on that day) and reorder_point. Days without a movement row are not checked, and the opening balance on its own never counts as a breach. Order the rows by breach_date, then by sku.

Tables

Table: ReorderRule

ColumnType
skuvarchar
opening_qtyint
reorder_pointint

Primary key: sku.

One row per planned SKU: its balance before the first movement and its reorder point.

Table: DailyNetMovement

ColumnType
skuvarchar
move_datedate
net_qtyint

Primary key (together): sku, move_date.

The net units added (positive) or removed (negative) on a day; every sku is in ReorderRule.

Example

The rows must come back in the order shown.

ReorderRule

skuopening_qtyreorder_point
GRO-10018030
DAI-20025020
SNK-60011525
HOM-50014010

DailyNetMovement

skumove_datenet_qty
GRO-10012025-07-01-30
GRO-10012025-07-02-20
GRO-10012025-07-03-5
GRO-10012025-07-04-10
DAI-20022025-07-01-35
DAI-20022025-07-0240
SNK-60012025-07-0220
SNK-60012025-07-03-15
HOM-50012025-07-02-30

Output

skubreach_dateclosing_balancereorder_point
DAI-20022025-07-011520
GRO-10012025-07-032530
SNK-60012025-07-032025
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.

First Day a SKU's Stock Falls Below Its Reorder Point

Planning starts each SKU at an opening balance and applies its daily net movement (receipts minus issues, so it can be negative).

  • Difficulty: Hard
  • Topics: Window Functions, Joins
  • Dialect: MySQL
  • Problem: #393

Problem statement

Planning starts each SKU at an opening balance and applies its daily net movement (receipts minus issues, so it can be negative). The closing balance on a movement day is the opening balance plus every net movement up to and including that day.

For every SKU whose closing balance ever goes strictly below its reorder point on a movement day, return the first such day: sku, breach_date, closing_balance (on that day) and reorder_point. Days without a movement row are not checked, and the opening balance on its own never counts as a breach. Order the rows by breach_date, then by sku.

Tables

Table: ReorderRule

ColumnType
skuvarchar
opening_qtyint
reorder_pointint

Primary key: sku.

One row per planned SKU: its balance before the first movement and its reorder point.

Table: DailyNetMovement

ColumnType
skuvarchar
move_datedate
net_qtyint

Primary key: sku, move_date.

The net units added (positive) or removed (negative) on a day; every sku is in ReorderRule.

Examples

Example 1

ReorderRule

skuopening_qtyreorder_point
GRO-10018030
DAI-20025020
SNK-60011525
HOM-50014010

DailyNetMovement

skumove_datenet_qty
GRO-10012025-07-01-30
GRO-10012025-07-02-20
GRO-10012025-07-03-5
GRO-10012025-07-04-10
DAI-20022025-07-01-35
DAI-20022025-07-0240
SNK-60012025-07-0220
SNK-60012025-07-03-15
HOM-50012025-07-02-30

Output

skubreach_dateclosing_balancereorder_point
DAI-20022025-07-011520
GRO-10012025-07-032530
SNK-60012025-07-032025

How to solve First Day a SKU's Stock Falls Below Its Reorder Point

The balance is not stored; it is a running total that has to be rebuilt. For each SKU, SUM(net_qty) OVER (PARTITION BY sku ORDER BY move_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) adds up the movements to date, and adding opening_qty from the joined rule turns it into the closing balance. Dates are unique per SKU, so the frame is unambiguous.

Window functions are evaluated after WHERE, so the breach test closing_balance < reorder_point must happen one level up, in a second CTE. There, a ROW_NUMBER() in date order (or a MIN(move_date) per SKU) singles out the first breach. A balance that touches the reorder point exactly is not a breach, and a SKU that dips and recovers is reported at its first dip only. SKUs that are below their reorder point before any movement are not breaches by the statement's rule; they are reported only if a movement day closes below it.

The alternative computes the running balance with a correlated SUM over earlier days and then keeps the minimum breach date per SKU — quadratic per SKU, but the window version needs one sort. Filtering the movements before computing the running total would be wrong: every day's movement must enter the balance, breach or not.

Reference solution (MySQL)

WITH balances AS (
  SELECT m.sku, m.move_date, r.reorder_point,
         r.opening_qty + SUM(m.net_qty) OVER (PARTITION BY m.sku ORDER BY m.move_date
                                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS closing_balance
  FROM DailyNetMovement m
  JOIN ReorderRule r ON r.sku = m.sku
), breaches AS (
  SELECT sku, move_date, closing_balance, reorder_point,
         ROW_NUMBER() OVER (PARTITION BY sku ORDER BY move_date) AS nth
  FROM balances
  WHERE closing_balance < reorder_point
)
SELECT sku, move_date AS breach_date, closing_balance, reorder_point
FROM breaches
WHERE nth = 1
ORDER BY breach_date, sku

Another way

WITH balances AS (
  SELECT m.sku, m.move_date, r.reorder_point,
         r.opening_qty + (SELECT SUM(x.net_qty) FROM DailyNetMovement x WHERE x.sku = m.sku AND x.move_date <= m.move_date) AS closing_balance
  FROM DailyNetMovement m JOIN ReorderRule r ON r.sku = m.sku
)
SELECT b.sku, b.move_date AS breach_date, b.closing_balance, b.reorder_point
FROM balances b
WHERE b.closing_balance < b.reorder_point
  AND b.move_date = (SELECT MIN(c.move_date) FROM balances c WHERE c.sku = b.sku AND c.closing_balance < c.reorder_point)
ORDER BY breach_date, b.sku

← Longest Consecutive Stock-Out Run per SKU · Median Supplier Lead Time From Goods Inward →