Use date arithmetic with NULL handling and GROUP BY
need avg length of stay in days by department for inpatient encounters only. exclude anyone still admitted (null discharge). round to 1 decimal. capacity planning meeting in 20 min.
| 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.
Dates are stored as TEXT — you'll need to cast them to real dates before subtracting. LOS in days is one date minus the other
Filter rows first (inpatient only, discharged only), then aggregate with AVG grouped by department. Round the output
`SELECT d.department_name, ROUND(AVG(e.discharge_date::date - e.admit_date::date), ___) AS avg_los FROM fact_encounters e JOIN dim_departments d ON ___ WHERE e.encounter_type = ___ AND e.discharge_date IS NOT ___ GROUP BY ___`