High-Risk Inventory Items by Pantry

SQL Query:

SELECT
  fp.pantry_name,
  i.name             AS item_name,
  i.quantity         AS current_stock,
  COALESCE(d.recent_distributed,0) AS recent_distributed,
  COALESCE(do.recent_donated,0)     AS recent_donated,
  (COALESCE(d.recent_distributed,0)/(i.quantity+COALESCE(do.recent_donated,0))) AS risk_metric
FROM Food_Pantries fp
JOIN Inventory i ON fp.pantry_id = i.pantry_id
LEFT JOIN (
  SELECT pantry_id, supply_id, SUM(quantity) AS recent_distributed
  FROM Food_Distributions
  WHERE date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
  GROUP BY pantry_id, supply_id
) d ON i.pantry_id = d.pantry_id AND i.supply_id = d.supply_id
LEFT JOIN (
  SELECT pantry_id, supply_id, SUM(amount) AS recent_donated
  FROM Donations
  WHERE date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
  GROUP BY pantry_id, supply_id
) do ON i.pantry_id = do.pantry_id AND i.supply_id = do.supply_id
WHERE i.quantity < 10
  AND COALESCE(d.recent_distributed,0) > (i.quantity + COALESCE(do.recent_donated,0))
ORDER BY risk_metric DESC
Executing query… 0s