Two-table JOIN with conditional aggregation per group. Practice routing claims through dim_members to reach a member attribute, then computing a per-region denial rate with the same SUM(CASE) / COUNT pattern from M6 — applied across a different grouping dimension.
Pop health is investigating whether denials concentrate geographically. From fact_claims joined to dim_members, give me a per-region denial rate — region, total_claims, denied_claims, and denial_rate_pct (100.0 * denied / total, rounded to 1 decimal). Five regions in the membership: Northeast, Southeast, Midwest, Southwest, West. Sort by denial_rate_pct DESC so the highest-friction region reads first. If one region is materially above the others, that's a network-adequacy or member-education hypothesis we'll route to the regional health plan team.
| 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 |
|---|---|---|
| member_id | INT | PK |
| plan_id | INT | FK → dim_plans |
| age_band | TEXT | |
| sex | TEXT | |
| region | TEXT | |
| enrollment_start | TEXT | |
| enrollment_end | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two-table aggregate: claims joined to members on member_id, grouped by the region attribute on the member side. No plan or provider info needed — the question lives entirely on the member dimension.
Same denial-rate pattern as M6 — conditional aggregation for the numerator (a CASE-summing pattern that counts only the denied rows) over the row count for the denominator. The grouping dimension shifts from CPT to region. Float-promote the percentage so integer division doesn't zero out the small rates.
Four output columns. Whether a HAVING gate belongs here is a judgment call — think about whether any region in the membership has so few claims that a noise-floor filter would matter, or whether the volumes are uniformly large enough that the rate is already defensible.