Conditional aggregation + HAVING + multi-table chain. Denial rate per CPT is the canonical RCM diagnostic for finding the codes that drive denials operations work; the volume floor (HAVING claim_count >= N) is essential to filter out small-N noise where a single denial creates a misleadingly high rate.
Wednesday denials prevention review — Sarah wants the denial rate by CPT, but only for high-volume CPTs (200+ claims). Without the volume floor, a niche CPT with 5 claims and 1 denial would show a 20% denial rate — meaningless noise. From fact_claims joined back through fact_charges to dim_cpt, give me four columns: cpt_code, description, claim_count, denial_pct (rounded to 1 decimal). Sort by denial_pct descending so the worst offenders lead. HAVING claim_count >= 200 to keep the noise out.
| Column | Type | Key |
|---|---|---|
| claim_id | INT | PK |
| charge_id | INT | FK → fact_charges |
| payer_id | INT | FK → dim_payer |
| submission_date | TEXT | |
| claim_status | TEXT | |
| submitted_amount | REAL |
| Column | Type | Key |
|---|---|---|
| charge_id | INT | PK |
| patient_id | INT | FK → dim_patient |
| provider_id | INT | FK → dim_provider |
| cpt_code | TEXT | FK → dim_cpt |
| service_date | TEXT | |
| billed_amount | REAL | |
| posted_amount | REAL |
| Column | Type | Key |
|---|---|---|
| cpt_code | TEXT | PK |
| description | TEXT | |
| service_line | TEXT | |
| charge_amount | REAL | |
| rvu | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
Three-table chain: fact_claims joins to fact_charges (via charge_id) which joins to dim_cpt (via cpt_code). Aggregate at the cpt_code grain.
Two aggregates per group — COUNT(*) for the volume + SUM(CASE WHEN claim_status='denied' THEN 1 ELSE 0 END) for the denials. Float-promote the ratio with 100.0 BEFORE dividing.
HAVING filters AFTER the GROUP BY — that's where the volume floor goes (the COUNT can't be referenced in WHERE because it doesn't exist yet at row-scan time). Sort denial_pct DESC so the highest offenders lead the meeting.