203. Province Healthcare Cost Inequality

SELECT pn.province_name, AVG(CASE WHEN p.allergies IS NOT NULL THEN a.admission_cost END) AS avg_cost_with_allergies, AVG(CASE WHEN p.allergies IS NULL THEN a.admission_cost END) AS avg_cost_without_allergies, ROUND(((AVG(CASE WHEN p.allergies IS NOT NULL THEN a.admission_cost END) - AVG(CASE WHEN p.allergies IS NULL THEN a.admission_cost END)) / AVG(CASE WHEN p.allergies IS NULL THEN a.admission_cost END)) * 100, 2) AS cost_diff_percent
FROM patients p
JOIN admissions a ON p.patient_id = a.patient_id
JOIN province_names pn ON p.province_id = pn.province_id
GROUP BY pn.province_name
HAVING AVG(CASE WHEN p.allergies IS NOT NULL THEN a.admission_cost END) > AVG(CASE WHEN p.allergies IS NULL THEN a.admission_cost END) * 1.15
ORDER BY cost_diff_percent DESC;