Use RANK() window function with data normalization
Rank each medical specialty by total encounter volume. Normalize specialty names so 'Cardiology' and 'cardiology' aren't separate. Show specialty, encounter count, and rank. I need to know where to focus our recruiting — if Cardiology is our busiest specialty, we need another cardiologist.
Each hint you reveal reduces the XP you can earn. Try the query first.
Specialty lives in `dim_providers`, volume in `fact_encounters`. Casing inconsistency will split specialties if you don't normalize
Group by the normalized specialty, count encounters, then add a rank column via a window function that orders by that count
`SELECT LOWER(p.specialty) AS specialty, COUNT(*) AS encounter_count, ___() OVER (ORDER BY COUNT(*) ___) AS rank FROM fact_encounters e JOIN dim_providers p ON ___ GROUP BY ___`