JOIN + GROUP BY + COUNT + ORDER BY DESC + LIMIT — the canonical 'top N by frequency' pattern. All-rank version of the M21 Master mission, which restricts to Principal only.
Different cut from last week. The board narrative talks about *principal* diagnoses (M21 — top conditions that drove an admission), but for the coding department's continuing-ed planning I want the *most-documented* diagnoses, any rank — what codes is our coding team writing the most, period? Top 10 by raw frequency across all dx_types. Code, description, count. Order by count descending. This includes secondary comorbidities, so I expect HTN and hyperlipidemia to lead — they ride along on almost every chart.
| 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.
Same JOIN as M6 — fact_diagnoses to dim_icd on icd_code. No dx_type filter; we want all rank levels.
GROUP BY icd_code, icd_description with COUNT(*) AS dx_count. Output three columns: icd_code, icd_description, dx_count.
ORDER BY dx_count DESC, LIMIT 10 — Dr. Chen wants the top 10 only. Compare your top 10 to the Master M21 list — the differences (chronic comorbidity codes that don't drive admissions) are the lesson.