First aggregation on fact_charges joined to dim_cpt. Per-CPT charge volume is the headline shape behind every charge capture audit, every coding-pattern review, and every revenue rollup; every Medium / Hard / Expert mission downstream extends this skeleton.
Standing charge capture audit — I need the headline number for tomorrow's Wednesday review. Across the full 24-month window in fact_charges, give me the total charge count per CPT. Use cpt_code + description from dim_cpt so the slide reads cleanly. Three columns: cpt_code, description, charge_count. Sort by charge_count descending — high-volume E&M codes (99213, 99214) should lead. Don't filter on date or anything else; we want the full window.
| 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.
Two-table aggregate. You'll need fact_charges (the volume) joined to dim_cpt (the readable description), grouped by the CPT key.
Group by cpt_code (and the description, since description is in the SELECT and not aggregated). COUNT(*) aliased as charge_count. Three-column output.
Sort descending on charge_count. Office-visit E&M codes carry the volume in any group practice; procedure codes are higher dollars but lower volume.