Trova ciò che le dashboard non vedono.

Definisco il perimetro usando l’ultimo record valido per ogni articolo.

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;

Collego la classe articolo all’ultimo snapshot di inventario.

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;

Aggiungo i lead time senza scartare gli articoli non associati.

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);

Controllo duplicati e valori mancanti prima di confrontare i gruppi.

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;

Confronto la copertura di magazzino con le vendite degli ultimi 90 giorni.

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;

Verifico se i riordini aperti spiegano l’eccesso di 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;

Verifico se la Classe X resta un outlier dopo aver normalizzato la rotazione.

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

Collego i punteggi normalizzati alla domanda realmente osservata.

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;

Isolo le sedi il cui punteggio resta sopra la soglia prevista.

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;