Store SKUs At or Below Their Reorder Point

Easy
373.

Store SKUs At or Below Their Reorder Point

EasyBasics

MySQL

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

ColumnType
store_idint
skuvarchar
on_handint
reorder_pointint

Primary key (together): store_id, sku.

One row per product per store. reorder_point is NULL where the planner has not set one.

Example

The rows must come back in the order shown.

StockLevel

store_idskuon_handreorder_point
1GRO-10011220
1DAI-20023030
1SNK-600114060
2GRO-1001420
2BEV-30010NULL
2HOM-5001912
3PER-40012515

Output

store_idskuon_handreorder_pointunits_short
2GRO-100142016
1GRO-100112208
2HOM-50019123
1DAI-200230300
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.

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

ColumnType
store_idint
skuvarchar
on_handint
reorder_pointint

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_idskuon_handreorder_point
1GRO-10011220
1DAI-20023030
1SNK-600114060
2GRO-1001420
2BEV-30010NULL
2HOM-5001912
3PER-40012515

Output

store_idskuon_handreorder_pointunits_short
2GRO-100142016
1GRO-100112208
2HOM-50019123
1DAI-200230300

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, sku

Another 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, sku

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