Two-table JOIN with a status filter and a multi-column GROUP BY. Practice routing claims through dim_plans for plan attributes, applying AVG on a money column, and grouping on two dimensions — the canonical actuarial pricing cut.
Actuarial team is rebuilding the pricing model and needs a per-plan-type, per-funding average paid amount. From fact_claims joined to dim_plans, restrict to status = 'paid' (denied and pending claims have zero or in-flight paid_amt and would drag the average down), then give me plan_type, funding, claim_count, and avg_paid (rounded to 2 decimals). Sort by avg_paid DESC so the high-cost cells lead. Expect one row per (plan_type, funding) combination that actually has paid claims — not every cell is populated, that's fine.
| Column | Type | Key |
|---|---|---|
| claim_id | INT | PK |
| member_id | INT | FK → dim_members |
| provider_id | INT | FK → dim_providers_payer |
| plan_id | INT | FK → dim_plans |
| service_date | TEXT | |
| cpt_code | TEXT | |
| place_of_service | TEXT | |
| billed_amt | REAL | |
| allowed_amt | REAL | |
| paid_amt | REAL | |
| status | TEXT |
| Column | Type | Key |
|---|---|---|
| plan_id | INT | PK |
| plan_name | TEXT | |
| plan_type | TEXT | |
| funding | TEXT | |
| metal_tier | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two-table aggregate: claims joined to plans on plan_id. The status filter is a WHERE clause — restrict to paid before aggregating so pending/denied rows don't dilute the average.
GROUP BY two columns: plan_type and funding. The grain of the output is one row per (plan_type, funding) combination present in the paid-claims subset; cells with no paid claims won't appear.
AVG over paid_amt, rounded to 2 decimals (money convention). Sort descending on the average so the high-cost plan/funding combinations lead the report.