Use LAG() window function for period-over-period comparison
Show me monthly encounter volume with month-over-month change as a percentage. I want to see if we're trending up or down. Also — exclude encounters from the provider with the future hire date. That data is suspect and I don't want it in the trend.
| 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 |
| Column | Type | Key |
|---|---|---|
| provider_id | INT | PK |
| first_name | TEXT | |
| last_name | TEXT | |
| specialty | TEXT | |
| department_id | INT | FK → dim_departments |
| hire_date | TEXT | |
| is_active | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Aggregate monthly counts first. Then a window function that looks one row back chronologically gives you the prior month's count
Exclude the suspect provider with the future hire_date before aggregating, then compute MoM % from the current and lagged counts
`LAG(encounter_count, ___) OVER (ORDER BY ___, ___) AS prior_count` — then `ROUND(100.0 * (encounter_count - prior_count) / ___, 1) AS mom_change_pct`