Build a multi-CTE pipeline with window functions combining all prior concepts
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.
Each hint you reveal reduces the XP you can earn. Try the query first.
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
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
`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 ___`