First aggregation on fact_claims joined to dim_payer. Claim volume per payer is the field for every payer-mix dashboard; AR managers run this view every Monday to spot which payers are absorbing claim volume vs which are slow-paying.
Monday morning payer-mix view — Linda is reviewing the AR cleanup at 11. Give me total claim counts per payer over the full 24-month window, joined to dim_payer so we get the payer_name on the page (not just payer_id). Three columns: payer_name, plan_type, claim_count. Sort by claim_count descending so the high-volume payers lead — the bottom of the list is where collections-effort gets concentrated.
| 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 |
|---|---|---|
| payer_id | INT | PK |
| payer_name | TEXT | |
| plan_type | TEXT | |
| expected_pct_of_billed | REAL | |
| is_in_network | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two-table aggregate. fact_claims has the volume; dim_payer has the readable name and plan_type. Group by the payer's PK so the SELECT can surface name + plan_type from the same row.
COUNT(*) aliased as claim_count. No date filter — Linda wants the full 24-month window for the payer-mix baseline.
Descending sort on claim_count surfaces the high-volume payers up top; the bottom of the list is the agenda for collections follow-up.