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.
Anti-join pattern: keep every row in the department dimension, then surface the ones with no matching encounter.
LEFT JOIN from departments to encounters, then WHERE the encounter-side key IS NULL.
An INNER JOIN silently drops orphans. The LEFT direction must be from the dimension to the fact, not the other way around.