Provider Analytics Path · Mission 24 of 25Expert

Department scorecard

Use multiple CTEs to build a comprehensive multi-metric report

The Brief

Michael TranCEOslack-dm

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.

You'll practice

Multiple CTEsComprehensive report

Tables & columns available

dim_departmentsdim6 columns

Column	Type	Key
department_id	INT	PK
department_name	TEXT
department_type	TEXT
floor	INT
bed_count	INT
manager_name	TEXT

fact_encountersfact10 columns

Column	Type	Key
encounter_id	INT	PK
patient_id	INT	FK → dim_patients
provider_id	INT	FK → dim_providers
department_id	INT	FK → dim_departments
admit_date	TEXT
discharge_date	TEXT
diagnosis_code	TEXT
diagnosis_description	TEXT
encounter_type	TEXT
discharge_disposition	TEXT

fact_quality_metricsfact7 columns

Column	Type	Key
metric_id	INT	PK
encounter_id	INT	FK → fact_encounters
patient_id	INT	FK → dim_patients
metric_type	TEXT
metric_value	REAL
measurement_date	TEXT
reported_by	TEXT

Hints (3)

Each hint you reveal reduces the XP you can earn. Try the query first.

Hint 1

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

Hint 2

Normalize the three ED name variants into one label inside each CTE. Then JOIN all the CTEs in the final SELECT on that label

Hint 3

`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

← Previous

Mission 23

Mission 25