Find what your dashboards miss.

I define the perimeter using the latest valid record for each item.

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY recorded_at DESC) AS rn
  FROM inventory
) SELECT * FROM ranked WHERE rn = 1;

I attach the item class to the latest inventory snapshot.

SELECT i.item_id, i.stock_value, m.item_class
FROM latest_inventory i
JOIN item_master m USING (item_id)
WHERE i.stock_value > 0;

I add supplier lead times without discarding unmatched items.

SELECT i.item_id, s.lead_days
FROM latest_inventory i
LEFT JOIN suppliers s ON s.supplier_id = i.supplier_id
WHERE COALESCE(s.active, TRUE);

I check duplicates and missing values before comparing groups.

SELECT COUNT(*) AS rows, COUNT(DISTINCT item_id) AS items,
SUM(CASE WHEN stock_value IS NULL THEN 1 ELSE 0 END) AS missing
FROM latest_inventory;

I compare inventory coverage with the last 90 days of sales.

SELECT i.item_id, i.on_hand, SUM(s.quantity) AS sold_90d
FROM latest_inventory i
JOIN sales s ON s.item_id = i.item_id
GROUP BY i.item_id, i.on_hand;

I verify whether open replenishments explain the excess stock.

SELECT i.item_id, SUM(r.open_quantity) AS inbound
FROM latest_inventory i
LEFT JOIN replenishments r ON r.item_id = i.item_id
GROUP BY i.item_id;

I test whether Class X remains an outlier after normalizing turnover.

SELECT item_class, AVG(stock_value / NULLIF(turnover, 0)) AS score
FROM latest_inventory
GROUP BY item_class;

I connect normalized inventory scores to realized demand.

SELECT i.item_class, AVG(i.stock_value / NULLIF(s.sold_90d, 0)) AS score
FROM latest_inventory i
JOIN sales_90d s USING (item_id)
GROUP BY i.item_class;

I isolate locations whose score remains above their target.

SELECT i.location_id, AVG(i.stock_value / NULLIF(i.turnover, 0)) AS score
FROM latest_inventory i
JOIN location_targets t USING (location_id)
GROUP BY i.location_id, t.max_score
HAVING AVG(i.stock_value / NULLIF(i.turnover, 0)) > t.max_score;