Aggregate at the body_system grain with a computed % of total via a scalar subquery (or window function). First multi-aggregate query — count plus a percentage in the same SELECT. Float-promotion gotcha appears.
Strategic-planning week — I'm writing the service-line investment memo and need a quick high-level cut. Roll our diagnoses up to body system: how many diagnoses fall in each body system, and what percentage of all documented diagnoses does each system represent? Order by count descending so the dominant systems lead. I'd expect Circulatory and Respiratory in the top tier — that's the cardiopulmonary skew of an aging adult population.
| 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 two-table join as M6/M7 (fact_diagnoses to dim_icd), but group by the body system rollup instead of by individual code.
Percent of total = per-group count divided by the total count across all groups. The total can come from a scalar subquery on the same join. Float-promote the division so the percentage isn't truncated to 0.
Three output columns: the body system, the count, and the percentage. Round the percentage to one decimal; sort by count descending.