Pantry Donations vs Distributions

SQL Query:


SELECT
  fp.pantry_name,
  COALESCE(dn.donated,0)       AS total_donated,
  COALESCE(ds.distributed,0)   AS total_distributed,
  (COALESCE(ds.distributed,0) / GREATEST(COALESCE(dn.donated,0),1)) AS distribution_ratio
FROM Food_Pantries fp
LEFT JOIN (
  SELECT pantry_id, SUM(amount) AS donated
  FROM Donations
  WHERE date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
  GROUP BY pantry_id
) dn ON fp.pantry_id = dn.pantry_id
LEFT JOIN (
  SELECT pantry_id, SUM(quantity) AS distributed
  FROM Food_Distributions
  WHERE date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
  GROUP BY pantry_id
) ds ON fp.pantry_id = ds.pantry_id
ORDER BY distribution_ratio DESC

Executing query… 0s

Pantry	Total Donated	Total Distributed	Dist/Don Ratio
Downtown Pantry TEST	550.00	18	0.03
Eastside Pantry	330.00	5	0.02
West End Pantry	220.00	2	0.01

Pantry Performance: Donations vs Distributions (Last Year)