Use LEFT JOIN with IS NULL to find orphaned dimension records
Dr. Okafor asked me to help audit the master data. Are there any departments in our system that have zero encounters? If so, we should either start using them or remove them from the system. Can you check?
| Column | Type | Key |
|---|---|---|
| department_id | INT | PK |
| department_name | TEXT | |
| department_type | TEXT | |
| floor | INT | |
| bed_count | INT | |
| manager_name | TEXT |
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
An INNER JOIN silently drops rows with no match. You need the join variant that keeps every department regardless
After the outer join, filter for rows where no encounter was matched — the right-side key will be missing
`SELECT d.department_name, d.department_type FROM dim_departments d ___ JOIN fact_encounters e ON d.department_id = e.department_id WHERE e.encounter_id IS ___`