First multi-table chain across charges → claims → remits, plus provider attribution. Provider-level paid revenue is the headline KPI on every CFO scorecard and every provider RVU compensation view; the chain (charge → claim → remit) is the load-bearing skeleton every RCM mission downstream extends.
End-of-month review — Linda wants total payments collected per provider for the comp committee. The chain: fact_charges has the provider_id, fact_claims joins via charge_id, fact_remits joins via claim_id. Sum paid_amount from fact_remits, group by provider, join dim_provider for the readable name + department. Four columns: provider_name, department, total_paid, claim_count. Sort by total_paid descending so the highest-revenue providers lead.
| 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 |
|---|---|---|
| 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 |
|---|---|---|
| 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 |
|---|---|---|
| provider_id | INT | PK |
| provider_name | TEXT | |
| specialty | TEXT | |
| department | TEXT | |
| is_credentialed | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Multi-table chain. You'll need fact_charges (the provider attribution), fact_claims (the bridge), fact_remits (the dollars), and dim_provider (the readable name + department). Four-table INNER JOIN.
Aggregate at provider grain. SUM the paid_amount from fact_remits and COUNT the claims. GROUP BY the provider's PK so the SELECT can surface name + department from the same row.
Float-point dollar sum + integer claim count in the same row. Sort descending on total_paid; the highest-revenue providers lead the comp committee discussion.