Use multiple CTEs to build a comprehensive multi-metric report
I need a department-level scorecard for the board. For each department, show: total encounters, average length of stay for inpatient discharges, average satisfaction score (exclude bad values), readmission rate, and complication rate. Combine the three ED name variants into one row. This is going to the board — it needs to be bulletproof.
Each hint you reveal reduces the XP you can earn. Try the query first.
Build one CTE per metric (encounters, LOS, satisfaction, readmissions, complications). Every CTE needs a normalized department-name key so they all join on the same thing
Normalize the three ED name variants into one label inside each CTE. Then JOIN all the CTEs in the final SELECT on that label
`CASE WHEN department_name IN ('Emergency', ___, ___) THEN 'Emergency' ELSE ___ END AS dept_group` — every CTE exposes `dept_group`, and the final query joins on it