Inventory & Replenishment Exceptions: handouts

Choose Print / Save as PDF, then select Save as PDF in your browser. The learning copy hides answers.

Inventory & Replenishment Exceptions

Original practice materials · Version 2.0.0 · Synthetic business data

Practice database

CREATE TABLE stock (sku TEXT, on_hand INTEGER, reserved INTEGER, reorder_point INTEGER, target INTEGER, pack_size INTEGER);
INSERT INTO stock VALUES
('SKU-100',NULL,0,10,30,4),
('SKU-101',11,1,10,35,6),
('SKU-102',0,0,10,40,10),
('SKU-103',1,4,10,30,4),
('SKU-104',4,4,10,35,6),
('SKU-105',5,5,10,40,10),
('SKU-106',6,6,10,30,4),
('SKU-107',7,7,10,35,6);
CREATE TABLE inbound (po_id TEXT, sku TEXT, units INTEGER, eta TEXT, status TEXT);
INSERT INTO inbound VALUES
('PO-100','SKU-100',4,'2026-10-01','cancelled'),
('PO-101','SKU-101',5,'2026-10-02','confirmed'),
('PO-102','SKU-102',6,'2026-10-03','confirmed'),
('PO-103','SKU-103',7,'2026-10-04','confirmed'),
('PO-104','SKU-104',8,'2026-10-05','confirmed'),
('PO-105','SKU-105',9,'2026-10-06','confirmed'),
('PO-106','SKU-106',10,'2026-10-07','confirmed'),
('PO-101','SKU-101',5,'2026-10-02','confirmed'),
('PO-102','SKU-102',999,'2026-10-03','confirmed'),
('EDGE-100','SKU-104',7,'2026-10-07','confirmed'),
('ORPH-100','UNKNOWN',40,'2026-10-07','confirmed');

1. Separate unknown stock from zero

Identify counts that need investigation before ordering.

NULL on_hand means the physical count is unknown. Zero means the count is known to be zero.
Subtract reserved units only from a known physical count.
A negative available count is a reservation conflict; do not silently convert it to zero.

List products needing a physical count

Return sku sorted ascending for NULL on_hand only.
Exclude known zero counts.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

SELECT sku FROM stock WHERE on_hand IS NULL ORDER BY sku

Unknown counts need a recount. A zero stock value is a measured quantity.

Calculate available units

Return sku, available for every stock SKU, sorted by sku.
available = on_hand - reserved. Preserve NULL and negative results.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

SELECT sku,on_hand-reserved AS available FROM stock ORDER BY sku

Clipping or defaulting would hide the cases that operations needs to investigate.

Extension: Check how a blanket COALESCE(on_hand,0) would create unjustified purchase orders.

2. Validate inbound purchase orders

Create one defensible inbound record per PO key.

A purchase order can be replayed by an integration.
Exact duplicate POs count once; conflicting PO fields exclude the entire key.
Only confirmed orders arriving by the declared cutoff can affect a replenishment decision.

Find inbound PO conflicts

Return po_id once for keys with conflicting sku, units, eta or status. Sort po_id.
All inbound fields are non-NULL; exact repeated copies are not conflicts.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

SELECT po_id FROM inbound GROUP BY po_id HAVING MIN(sku)<>MAX(sku) OR MIN(units)<>MAX(units) OR MIN(eta)<>MAX(eta) OR MIN(status)<>MAX(status) ORDER BY po_id

Do not mistake retry count for a conflict. Compare facts under the same PO ID.

Total trusted arrivals by SKU

Return sku, due_units sorted by sku.
Deduplicate exact POs and exclude conflicts. Include confirmed eta <= 2026-10-07. This raw inbound summary includes unmatched SKUs.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH clean_inbound AS (SELECT DISTINCT p.* FROM inbound p WHERE NOT EXISTS (SELECT 1 FROM inbound x WHERE x.po_id=p.po_id AND (x.sku<>p.sku OR x.units<>p.units OR x.eta<>p.eta OR x.status<>p.status))) SELECT sku,SUM(units) AS due_units FROM clean_inbound WHERE status='confirmed' AND eta<='2026-10-07' GROUP BY sku ORDER BY sku

The source summary keeps an orphan visible; the later stock join determines which products receive it.

Extension: Change the cutoff and rerun the query without editing individual results.

3. Project stock without losing exceptions

Join trusted arrivals while keeping the stock master complete.

The stock master owns the product cohort.
A LEFT JOIN preserves products with no purchase orders.
Default missing due units to zero while leaving unknown physical stock NULL.

List unmatched inbound product keys

Return sku once for canonical inbound records absent from stock. Sort sku.
Inspect all canonical inbound regardless of status or arrival date.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH clean_inbound AS (SELECT DISTINCT p.* FROM inbound p WHERE NOT EXISTS (SELECT 1 FROM inbound x WHERE x.po_id=p.po_id AND (x.sku<>p.sku OR x.units<>p.units OR x.eta<>p.eta OR x.status<>p.status))) SELECT DISTINCT p.sku FROM clean_inbound p LEFT JOIN stock s ON s.sku=p.sku WHERE s.sku IS NULL ORDER BY p.sku

Do not create a stock row from an unmatched receipt or lose it in an inner join.

Build projected stock positions

Return sku, available, due_units, position for every stock SKU sorted ascending.
Use canonical confirmed arrivals through 2026-10-07 inclusive. available = on_hand - reserved; position = available + due_units.
Missing inbound is 0; unknown available and position stay SQL NULL.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH clean_inbound AS (SELECT DISTINCT p.* FROM inbound p WHERE NOT EXISTS (SELECT 1 FROM inbound x WHERE x.po_id=p.po_id AND (x.sku<>p.sku OR x.units<>p.units OR x.eta<>p.eta OR x.status<>p.status))), due AS (SELECT sku,SUM(units) AS due_units FROM clean_inbound WHERE status='confirmed' AND eta<='2026-10-07' GROUP BY sku), positions AS (SELECT s.*,s.on_hand-s.reserved AS available,COALESCE(d.due_units,0) AS due_units,s.on_hand-s.reserved+COALESCE(d.due_units,0) AS position FROM stock s LEFT JOIN due d ON s.sku=d.sku) SELECT sku,available,due_units,position FROM positions ORDER BY sku

COALESCE belongs on the missing inbound total; putting it on physical stock invents a measurement.

Extension: Compare the product count before and after the join to catch duplicated keys.

4. Order complete packs with exception priority

Choose an action and a supplier-valid quantity.

Resolve unknown counts and negative available before considering reorder thresholds.
Reorder only below the point; equality means hold.
Round the positive target gap up to a multiple of pack_size.

Flag replenishment actions

Return sku, action sorted by sku.
Priority: NULL stock → recount; negative available → resolve_reservations; position < reorder_point → reorder; otherwise hold.
Use canonical confirmed inbound through 2026-10-07 inclusive.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH clean_inbound AS (SELECT DISTINCT p.* FROM inbound p WHERE NOT EXISTS (SELECT 1 FROM inbound x WHERE x.po_id=p.po_id AND (x.sku<>p.sku OR x.units<>p.units OR x.eta<>p.eta OR x.status<>p.status))), due AS (SELECT sku,SUM(units) AS due_units FROM clean_inbound WHERE status='confirmed' AND eta<='2026-10-07' GROUP BY sku), positions AS (SELECT s.*,s.on_hand-s.reserved AS available,COALESCE(d.due_units,0) AS due_units,s.on_hand-s.reserved+COALESCE(d.due_units,0) AS position FROM stock s LEFT JOIN due d ON s.sku=d.sku) SELECT sku,CASE WHEN on_hand IS NULL THEN 'recount' WHEN available<0 THEN 'resolve_reservations' WHEN position<reorder_point THEN 'reorder' ELSE 'hold' END AS action FROM positions ORDER BY sku

Changing the CASE order can incorrectly order a product whose reservation count is invalid.

Calculate complete-pack orders

Return sku, order_qty for every SKU sorted ascending.
Unknown stock or negative available gives SQL NULL. Otherwise position below the point orders the target gap rounded up to complete packs; hold gives 0.
Use canonical confirmed inbound through 2026-10-07 inclusive.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH clean_inbound AS (SELECT DISTINCT p.* FROM inbound p WHERE NOT EXISTS (SELECT 1 FROM inbound x WHERE x.po_id=p.po_id AND (x.sku<>p.sku OR x.units<>p.units OR x.eta<>p.eta OR x.status<>p.status))), due AS (SELECT sku,SUM(units) AS due_units FROM clean_inbound WHERE status='confirmed' AND eta<='2026-10-07' GROUP BY sku), positions AS (SELECT s.*,s.on_hand-s.reserved AS available,COALESCE(d.due_units,0) AS due_units,s.on_hand-s.reserved+COALESCE(d.due_units,0) AS position FROM stock s LEFT JOIN due d ON s.sku=d.sku) SELECT sku,CASE WHEN on_hand IS NULL OR available<0 THEN NULL WHEN position<reorder_point THEN ((target-position+pack_size-1)/pack_size)*pack_size ELSE 0 END AS order_qty FROM positions ORDER BY sku

A rounded-down purchase does not reach target. A numeric order for an unknown count is not defensible.

Extension: Check that the ordered amount reaches target but removing one pack would not.

Final project

Handoff a replenishment and exception queue

Return sku, available, due_units, position, action, order_qty, sorted by sku. Keep every stock SKU once.
Eligible inbound is confirmed with eta <= 2026-10-07, including overdue arrivals and arrivals exactly on the cutoff. Collapse identical PO copies; exclude all variants if a PO key has conflicting fields. Ignore unmatched inbound SKUs.
available = on_hand - reserved. due_units is the eligible inbound sum, defaulting to zero. position = available + due_units; NULL on_hand leaves available and position NULL.
Action priority: NULL on_hand gives recount and NULL order_qty; negative available gives resolve_reservations and NULL order_qty; otherwise position < reorder_point gives reorder; equality or above gives hold.
For reorder, round target - position up to a whole number of pack_size units. For hold return numeric 0. Do not turn unknown stock into zero.

Use the complete project source data in the course download. Every output check must pass.

WITH clean_inbound AS (SELECT DISTINCT p.* FROM inbound p WHERE NOT EXISTS (SELECT 1 FROM inbound x WHERE x.po_id=p.po_id AND (x.sku<>p.sku OR x.units<>p.units OR x.eta<>p.eta OR x.status<>p.status))), due AS (SELECT sku,SUM(units) AS due_units FROM clean_inbound WHERE status='confirmed' AND eta<='2026-10-07' GROUP BY sku), positions AS (SELECT s.*,s.on_hand-s.reserved AS available,COALESCE(d.due_units,0) AS due_units,s.on_hand-s.reserved+COALESCE(d.due_units,0) AS position FROM stock s LEFT JOIN due d ON s.sku=d.sku) SELECT sku,available,due_units,position,CASE WHEN on_hand IS NULL THEN 'recount' WHEN available<0 THEN 'resolve_reservations' WHEN position<reorder_point THEN 'reorder' ELSE 'hold' END AS action,CASE WHEN on_hand IS NULL OR available<0 THEN NULL WHEN position<reorder_point THEN ((target-position+pack_size-1)/pack_size)*pack_size ELSE 0 END AS order_qty FROM positions ORDER BY sku;

Related public objectives: https://sqlbolt.com/.

Independent original materials. CC BY 4.0.