Beneficiary Retention Rate

SQL Query:


SELECT
  fp.pantry_name,
  COUNT(DISTINCT b1.beneficiary_id) AS returned_count,
  COUNT(DISTINCT b_all.beneficiary_id) AS total_unique
FROM Food_Pantries fp
JOIN Food_Distributions fd1
  ON fp.pantry_id = fd1.pantry_id
  AND fd1.date BETWEEN DATE_SUB(CURDATE(),INTERVAL 180 DAY) AND DATE_SUB(CURDATE(),INTERVAL 90 DAY)
JOIN Beneficiaries b1
  ON fd1.beneficiary_id = b1.beneficiary_id
JOIN Food_Distributions fd2
  ON b1.beneficiary_id = fd2.beneficiary_id
  AND fd2.date > DATE_SUB(CURDATE(),INTERVAL 90 DAY)
JOIN Beneficiaries b_all
  ON b1.beneficiary_id = b_all.beneficiary_id
GROUP BY fp.pantry_name

Executing query… 0s

Beneficiary Retention Rate (6–3 Months Ago vs Last 3 Months)