Use CTEs, LAG with PARTITION BY, and TRIM for year-over-year analysis
For the top 10 diagnosis codes by total volume: show the code, description, encounters this year, encounters last year, and year-over-year change as a percentage. Clean up trailing spaces in diagnosis codes — the EHR issue we flagged before. I want to see if our case mix is shifting.
Each hint you reveal reduces the XP you can earn. Try the query first.
Build a CTE of yearly counts per trimmed diagnosis code. Use EXTRACT to pull the year from admit_date
Use LAG partitioned by diagnosis so each code is compared to its own prior year, not the row above. Then restrict to the top 10 by total volume
`LAG(encounter_count, 1) OVER (PARTITION BY ___ ORDER BY ___) AS prior_year` — plus a top-10 filter using the combined totals across years