Drill 3 of 12 in Logistics questions
0

Interview Question Bank · WHERE with NULLs · COUNT · MIN

3. Warehouse stock-out days

EasyLogisticsQuestion #159

Concept: WHERE with NULLs · COUNT · MINshow

A stock-out is a day with nothing to pick, and that includes oversold counts that went below zero, so test on_hand <= 0 rather than = 0. A NULL count means the number is unknown, not zero: a plain comparison with NULL is never true, so WHERE on_hand <= 0 already skips those days. Turning NULL into 0 with COALESCE invents stock-outs that may never have happened.

Example (different data):

SELECT station, COUNT(*) AS empty_days, MIN(reading_date) AS first_empty
FROM fuel_tank_readings
WHERE litres <= 0
GROUP BY station;

Your task

Larkspur Freight's inventory lead is reviewing the week of 2025-05-05 to find which warehouse bins ran dry. Counts are taken at the end of each day; a NULL means nobody counted, and a negative number means the bin was oversold.

For each warehouse and SKU that was out of stock (on_hand of 0 or less) on at least one day, return warehouse (code), sku, stockout_days and first_stockout (the earliest such snapshot_date). Uncounted days (NULL) are not stock-outs. Sort by stockout_days descending, then warehouse, then sku.

Starting Postgres in your browser…

Checking your access…