Donation Conversion Rate

SQL Query:


SELECT
  fp.pantry_name,
  COALESCE(reqs.cnt,0) AS total_requests,
  COALESCE(dns.cnt,0) AS total_donations,
  (COALESCE(dns.cnt,0)/ GREATEST(COALESCE(reqs.cnt,0),1)) AS conversion_rate
FROM Food_Pantries fp
LEFT JOIN (
  SELECT pantry_id, COUNT(*) AS cnt
  FROM Request_Log
  WHERE request_date >= DATE_SUB(CURDATE(),INTERVAL 1 YEAR)
  GROUP BY pantry_id
) reqs ON fp.pantry_id=reqs.pantry_id
LEFT JOIN (
  SELECT pantry_id, COUNT(*) AS cnt
  FROM Donations
  WHERE date >= DATE_SUB(CURDATE(),INTERVAL 1 YEAR)
  GROUP BY pantry_id
) dns ON fp.pantry_id=dns.pantry_id
ORDER BY conversion_rate DESC

Executing query… 0s

Pantry	Requests	Donations	Conversion
Eastside Pantry	1	2	200.0%
Downtown Pantry TEST	2	2	100.0%
West End Pantry	0	1	100.0%

Donation Conversion Rate (Requests⇒Donations)