SELECT
fp.pantry_name,
COUNT(pf.feedback_id) AS total_feedback,
AVG(LENGTH(pf.message)) AS avg_length,
SUM(CASE WHEN pf.rating>=4 THEN 1 ELSE 0 END)/COUNT(*) AS positive_rate
FROM Pantry_Feedback pf
JOIN Food_Pantries fp ON pf.pantry_id = fp.pantry_id
WHERE pf.submitted_on >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY fp.pantry_name
ORDER BY positive_rate DESC
| Pantry | # Feedback | Avg Msg Length | % Positive |
|---|