Drill 6 of 12 in Logistics questions
0

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…