Use GROUP BY with COUNT and ORDER BY to find top values
Break down our encounter volume by department. I need department name and encounter count, sorted by volume descending. This tells me where our patients are going and where to focus the quality review.
| 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 |
|---|---|---|
| department_id | INT | PK |
| department_name | TEXT | |
| department_type | TEXT | |
| floor | INT | |
| bed_count | INT | |
| manager_name | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Department names live in `dim_departments`, encounter rows in `fact_encounters`. You'll need to connect them on `department_id`
You want one row per department with a count beside it, sorted with the busiest first. Think grouping + aggregation + ordering
`SELECT d.department_name, ___(*) AS encounter_count FROM fact_encounters e JOIN dim_departments d ON ___ GROUP BY ___ ORDER BY ___ DESC`