Store SKUs At or Below Their Reorder Point — SQL Problem
Every evening the replenishment team pulls the shelf positions that need a fresh order.
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #373
Problem statement
Every evening the replenishment team pulls the shelf positions that need a fresh order. A position needs one when its stock on hand is at or below its reorder point. Some positions have no reorder point configured yet (reorder_point NULL); they are never flagged.
Return store_id, sku, on_hand, reorder_point and units_short (the reorder point minus the stock on hand, 0 when they are equal) for every flagged position. Order the rows by units_short highest first, then by store_id, then by sku.
Tables
Table: StockLevel
| Column | Type |
|---|---|
| store_id | int |
| sku | varchar |
| on_hand | int |
| reorder_point | int |
Primary key: store_id, sku.
One row per product per store. reorder_point is NULL where the planner has not set one.
Examples
Example 1
StockLevel
| store_id | sku | on_hand | reorder_point |
|---|---|---|---|
| 1 | GRO-1001 | 12 | 20 |
| 1 | DAI-2002 | 30 | 30 |
| 1 | SNK-6001 | 140 | 60 |
| 2 | GRO-1001 | 4 | 20 |
| 2 | BEV-3001 | 0 | NULL |
| 2 | HOM-5001 | 9 | 12 |
| 3 | PER-4001 | 25 | 15 |
Output
| store_id | sku | on_hand | reorder_point | units_short |
|---|---|---|---|---|
| 2 | GRO-1001 | 4 | 20 | 16 |
| 1 | GRO-1001 | 12 | 20 | 8 |
| 2 | HOM-5001 | 9 | 12 | 3 |
| 1 | DAI-2002 | 30 | 30 | 0 |
How to solve Store SKUs At or Below Their Reorder Point
This is a single-table filter with one computed column. A position needs an order when on_hand <= reorder_point; the <= matters, because a shelf sitting exactly on its reorder point is the moment the planner wants to order, and units_short is then 0.
Positions with a NULL reorder point drop out by themselves: 12 <= NULL is unknown, not true, and WHERE keeps only rows whose condition is true. Writing reorder_point IS NOT NULL explicitly, as one alternative does, says the same thing more loudly. Beware the opposite trick — NOT (on_hand > reorder_point) is also unknown for NULL, so it is safe here too, but a COALESCE(reorder_point, 0) would wrongly flag empty unconfigured shelves.
The order needs three keys so that two positions with the same shortfall come out in a fixed order. The query reads the table once; with an index on (store_id, sku) the sort is the only extra cost.
Reference solution (MySQL)
SELECT store_id, sku, on_hand, reorder_point,
reorder_point - on_hand AS units_short
FROM StockLevel
WHERE on_hand <= reorder_point
ORDER BY units_short DESC, store_id, skuAnother way
SELECT store_id, sku, on_hand, reorder_point, reorder_point - on_hand AS units_short FROM StockLevel WHERE reorder_point IS NOT NULL AND NOT (on_hand > reorder_point) ORDER BY reorder_point - on_hand DESC, store_id, skuAnother way
SELECT store_id, sku, on_hand, reorder_point, reorder_point - on_hand AS units_short FROM StockLevel WHERE reorder_point - on_hand >= 0 ORDER BY 5 DESC, 1, 2← Six-Month Renter Retention by Move-In Cohort · Suppliers With No Purchase Order in 2024 →