JOIN + WHERE on a flag column from the joined dim table + GROUP BY + LIMIT. Prepares the cohort-on-flag pattern that the Master M22 (HF-as-principal) and M25 (chronic burden capstone) build on.
Chronic-disease registry roster prep. Pull our top 10 most-documented chronic conditions — code, description, and the count of times we've documented each. Filter on the is_chronic flag in dim_icd, not on a list of codes (that flag is the source of truth; hand-rolled lists drift). Order by count descending. The roster lead I get from this drives the proactive-outreach call list for the next quarter.
| 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.
JOIN fact_diagnoses to dim_icd as before. WHERE d.is_chronic = 1 — that's the filter on the joined table.
GROUP BY d.icd_code, d.icd_description. COUNT(*) AS dx_count.
ORDER BY dx_count DESC, LIMIT 10. Three columns: icd_code, icd_description, dx_count. Same skeleton as M7 with one added WHERE clause.