Provider Analytics Path · Mission 25 of 25Expert

The CMS Quality Dashboard

Build a multi-CTE pipeline with window functions combining all prior concepts

The Brief

Michael TranCEOslack-dm

This is it. The board meets Wednesday. I need one query that produces a 6-month trend of our four key quality metrics: 30-day readmission rate, average ED wait time (filtered for bad data), average patient satisfaction, and complication rate. Show year, month, and all four metrics. Include a 3-month moving average for the readmission rate. The CMS reviewer will see this — every number has to be right.

You'll practice

Multi-CTE pipelineWindow framesCapstone

Tables & columns available

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

dim_departmentsdim6 columns

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

dim_patientsdim9 columns

Column	Type	Key
patient_id	INT	PK
first_name	TEXT
last_name	TEXT
date_of_birth	TEXT
gender	TEXT
insurance_type	TEXT
zip_code	TEXT
primary_language	TEXT
is_deceased	INT

Hints (3)

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

Hint 1

One CTE per metric, each keyed on (year, month). Every data quality lesson from the path applies here — audit your filters for bad waits, bad satisfaction, ED name variants, insurance casing, TRIM

Hint 2

The outer query joins the metric CTEs on (year, month), adds a windowed moving average to the readmission rate, and returns only the last 6 months

Hint 3

`AVG(readmission_rate) OVER (ORDER BY year, month ROWS BETWEEN ___ PRECEDING AND CURRENT ROW) AS readmission_moving_avg` — then `ORDER BY year DESC, month DESC LIMIT ___`

← Previous

Mission 24