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…