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.
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`