208. Age Group Healthcare Utilization

WITH age_groups AS (
    SELECT p.patient_id, CASE WHEN (JULIANDAY('now') - JULIANDAY(p.birth_date)) / 365 < 19 THEN '0-18' WHEN (JULIANDAY('now') - JULIANDAY(p.birth_date)) / 365 < 36 THEN '19-35' WHEN (JULIANDAY('now') - JULIANDAY(p.birth_date)) / 365 < 51 THEN '36-50' WHEN (JULIANDAY('now') - JULIANDAY(p.birth_date)) / 365 < 66 THEN '51-65' ELSE '66+' END AS age_group
    FROM patients p
)
SELECT ag.age_group, COUNT(DISTINCT ag.patient_id) AS total_patients, ROUND(AVG(admission_counts.admission_count), 2) AS avg_admissions_per_patient, ROUND(AVG(admission_costs.total_cost), 2) AS avg_cost_per_patient, diagnosis_counts.diagnosis AS most_common_diagnosis
FROM age_groups ag
LEFT JOIN (SELECT patient_id, COUNT(*) AS admission_count FROM admissions GROUP BY patient_id) admission_counts ON ag.patient_id = admission_counts.patient_id
LEFT JOIN (SELECT patient_id, SUM(admission_cost) AS total_cost FROM admissions GROUP BY patient_id) admission_costs ON ag.patient_id = admission_costs.patient_id
LEFT JOIN (SELECT a.patient_id, a.diagnosis, COUNT(*) AS diag_count FROM admissions a GROUP BY a.patient_id, a.diagnosis) diagnosis_counts ON ag.patient_id = diagnosis_counts.patient_id
GROUP BY ag.age_group
ORDER BY CASE ag.age_group WHEN '0-18' THEN 1 WHEN '19-35' THEN 2 WHEN '36-50' THEN 3 WHEN '51-65' THEN 4 ELSE 5 END;