First step into Medium tier — 4-table chain (charges → claims → remits + dim_cpt) with SUM, ORDER BY DESC, LIMIT. The canonical 'top revenue drivers' shape that opens every brand-new RCM director's first weekly review. The on-ramp from Easy aggregations to the percent / NTILE / RANK patterns in Hard tier (M11-M14).
New denials role this quarter — Sarah asked me to start by understanding which CPT codes carry the most paid revenue, since those are the codes a 1% denial rate hits hardest in dollar terms. Pull from fact_remits joined back through claims and charges to dim_cpt; output cpt_code, description, total_revenue (SUM of paid_amount, rounded to 2 decimals). Sort by total_revenue descending. Top 10 only — that's the focus list for our denials prevention plan.
| Column | Type | Key |
|---|---|---|
| remit_id | INT | PK |
| claim_id | INT | FK → fact_claims |
| posted_date | TEXT | |
| allowed_amount | REAL | |
| paid_amount | REAL | |
| adjustment_amount | REAL | |
| denial_code | TEXT | |
| denial_reason | TEXT |
| 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.
Multi-table chain. Start at fact_remits (the dollars), bridge through fact_claims and fact_charges to reach dim_cpt for the readable description.
Aggregate at CPT grain. SUM the paid_amount across all matching remits, group by cpt_code (and description, since description is in the SELECT and not aggregated).
Top-N pattern: ROUND the dollar sum to 2 decimals, sort descending on total_revenue with cpt_code ASC as a stable tiebreaker, then cap with LIMIT 10.