Two-column GROUP BY with a HAVING gate, sorted hierarchically. Practice the categorical-frequency pattern that drives every root-cause prioritization deck — group by category and code together, gate small-N noise, sort so each category's leader sits at the top of its block.
Quarterly root-cause review is Friday. From fact_denials, give me the denial counts grouped by denial_category and carc_code — three columns: denial_category, carc_code, denial_count. Filter out (category, code) pairs with fewer than 10 denials so the long tail doesn't drown the workstream signal. Sort by denial_category alphabetically, then by denial_count DESC inside each category — that way the leader CARC for each category sits at the top of its block and reads like a workstream agenda. We use the top code in each category as the kickoff topic for the corresponding root-cause team.
| Column | Type | Key |
|---|---|---|
| denial_id | INT | PK |
| claim_id | INT | FK → fact_claims |
| denied_date | TEXT | |
| carc_code | TEXT | |
| rarc_code | TEXT | |
| denial_category | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Single-table aggregate on fact_denials. GROUP BY two columns: denial_category and carc_code. The grain is one row per (category, code) pair.
Volume gate goes in HAVING (post-aggregation), not WHERE — you can't filter on COUNT(*) until after the GROUP BY collapses the rows.
Hierarchical sort: denial_category ascending (alphabetic block order), denial_count descending inside each block. That's the shape the root-cause team reads — leader code first inside each category.