First two-table INNER JOIN. Connect fact_diagnoses to dim_icd on icd_code and produce a readable chart-view of every documented diagnosis. Builds the foundational terminology join with no aggregation in the way.
Quick ad-hoc — the EHR vendor's pulling a sample export for the new analytics platform PoC and they want the diagnosis log in human-readable form, not raw codes. Pull every row from fact_diagnoses and join dim_icd so each diagnosis shows the encounter_id, the icd_code, the description, the CCSR category, and the dx_type. Sort by encounter_id and then dx_rank so the chart for each encounter reads top-to-bottom (Principal first, then secondaries).
| Column | Type | Key |
|---|---|---|
| diagnosis_id | INT | PK |
| encounter_id | INT | FK → fact_encounters |
| icd_code | TEXT | FK → dim_icd |
| dx_type | TEXT | |
| poa_indicator | TEXT | |
| dx_rank | INT |
| Column | Type | Key |
|---|---|---|
| icd_code | TEXT | PK |
| icd_description | TEXT | |
| ccsr_category | TEXT | |
| ccsr_description | TEXT | |
| body_system | TEXT | |
| is_chronic | INT | |
| hcc_flag | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
INNER JOIN fact_diagnoses to dim_icd on icd_code. The match column has the same name in both tables — use a table alias and `ON fd.icd_code = d.icd_code`.
Five columns out: encounter_id, icd_code, icd_description, ccsr_description, dx_type. No filters; the export wants every row.
ORDER BY encounter_id, dx_rank. Within each encounter, dx_rank=1 (Principal) leads, then secondaries 2, 3, etc.