Use CTEs, window frames (ROWS BETWEEN), LAG, and CASE flags
Show monthly average patient satisfaction scores with a 3-month moving average alongside. Filter out satisfaction scores of 0 or negative — those are clearly data errors. Also flag any month where satisfaction dropped more than 10% from the prior month. I'm presenting to the patient advisory council next week.
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
CTE for monthly averages with the bad values filtered out. The outer query adds a windowed moving average and a drop flag
A moving average needs a window frame that includes the current row and the prior two. The drop flag compares current to the lagged value
`AVG(avg_score) OVER (ORDER BY year, month ROWS BETWEEN ___ PRECEDING AND ___ ROW) AS moving_avg_3m` — flag via `CASE WHEN (avg_score - LAG(avg_score) OVER(...)) / LAG(avg_score) OVER(...) < ___ THEN 'DROP' END`