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