Interview Question Bank · Latest snapshot · SUM across locations · LEFT JOIN
6. Reorder alerts with supplier lead times
MediumLogisticsQuestion #162
Concept: Latest snapshot · SUM across locations · LEFT JOINshow
A daily snapshot table holds one row per day, so current stock is the rows for the latest date only; summing every day counts the same units over and over. A network-wide threshold compares against the total across all locations, so SUM before comparing. Start from the item list and LEFT JOIN the stock: an item with no stock rows at all is the most urgent, and an inner join silently drops it.
Example (different data):
WITH today AS ( SELECT drug_id, SUM(units) AS units FROM pharmacy_stock WHERE counted_on = DATE '2025-02-28' GROUP BY drug_id ) SELECT d.name, COALESCE(t.units, 0) AS units FROM drugs d LEFT JOIN today t ON t.drug_id = d.id WHERE COALESCE(t.units, 0) < d.min_units;
Your task
Larkspur Freight reorders packaging supplies when a SKU's total stock across all warehouses falls below its reorder point. The planner wants this morning's alert list, based on the latest stock count (2025-05-09), with the date each order would arrive if placed that day.
Using only the 2025-05-09 snapshot, return sku, product (name), on_hand (total across warehouses, 0 if the SKU has no stock rows), reorder_point, supplier (name), lead_time_days and expected_arrival (2025-05-09 plus lead_time_days) for every SKU whose on_hand is below its reorder_point. Sort by expected_arrival, then sku.
Starting Postgres in your browser…
Checking your access…