Compute a per-group denial rate with conditional aggregation. Practice the SUM(CASE WHEN ...) / COUNT(*) pattern, the float-promotion trick that keeps percentages from collapsing to zero, and the HAVING gate that filters out small-N noise — the canonical shape of every revenue-cycle hot-list query.
Need a CPT hot list for the prior-auth standup. From fact_claims, give me the ten CPT codes with the highest denial rate. Filter out low-volume codes — only include cpt_codes with at least 30 claims, otherwise a code with 2/3 denials reads at 66% and crowds out the real signal. Sort so the highest-friction codes lead, with claim volume as the tiebreaker so high-volume codes outrank low-volume codes at the same percentage. Top of the list is where coding-hygiene and prior-auth gaps live; that's the workstream I'm staffing this quarter.
| Column | Type | Key |
|---|---|---|
| claim_id | INT | PK |
| member_id | INT | FK → dim_members |
| provider_id | INT | FK → dim_providers_payer |
| plan_id | INT | FK → dim_plans |
| service_date | TEXT | |
| cpt_code | TEXT | |
| place_of_service | TEXT | |
| billed_amt | REAL | |
| allowed_amt | REAL | |
| paid_amt | REAL | |
| status | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Single-table aggregate over fact_claims. The denominator is the row count per CPT; the numerator is the count of denied rows in the same group — that's a SUM over a CASE expression that returns 1 when status is denied and 0 otherwise.
The denial-rate ratio needs float promotion. Multiplying by 100.0 (with the decimal) before dividing forces float math; integer division silently rounds every rate under 1 to zero and the whole report comes back empty at the top.
HAVING gates groups after aggregation — that's where the min-30-claims filter goes, not WHERE. The sort is two-key (the rate, then a tiebreaker) so high-volume codes outrank low-volume codes when the percentage is identical; the brief tells you which direction each key goes. LIMIT cuts the final list to the top tier.