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
| Column | Type |
|---|---|
| sku | varchar |
| opening_qty | int |
| reorder_point | int |
Primary key: sku.
One row per planned SKU: its balance before the first movement and its reorder point.
Table: DailyNetMovement
| Column | Type |
|---|---|
| sku | varchar |
| move_date | date |
| net_qty | int |
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
| sku | opening_qty | reorder_point |
|---|---|---|
| GRO-1001 | 80 | 30 |
| DAI-2002 | 50 | 20 |
| SNK-6001 | 15 | 25 |
| HOM-5001 | 40 | 10 |
DailyNetMovement
| sku | move_date | net_qty |
|---|---|---|
| GRO-1001 | 2025-07-01 | -30 |
| GRO-1001 | 2025-07-02 | -20 |
| GRO-1001 | 2025-07-03 | -5 |
| GRO-1001 | 2025-07-04 | -10 |
| DAI-2002 | 2025-07-01 | -35 |
| DAI-2002 | 2025-07-02 | 40 |
| SNK-6001 | 2025-07-02 | 20 |
| SNK-6001 | 2025-07-03 | -15 |
| HOM-5001 | 2025-07-02 | -30 |
Output
| sku | breach_date | closing_balance | reorder_point |
|---|---|---|---|
| DAI-2002 | 2025-07-01 | 15 | 20 |
| GRO-1001 | 2025-07-03 | 25 | 30 |
| SNK-6001 | 2025-07-03 | 20 | 25 |
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, skuAnother 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 →