Two-table join from fact to dimension to surface a categorical attribute, then ranked LIMIT output. Practice JOIN to dim_providers_payer on provider_id, GROUP BY on the dimension attribute, ORDER BY DESC, LIMIT — the ranked-list pattern behind every network-management report.
Network team is mapping where contracting spend goes next quarter. From fact_claims joined to dim_providers_payer, give me the top 5 specialties ranked by claim count — specialty and claim_count, descending. The provider table has the specialty attribute; claims only carry the provider key. This drives our renegotiation priorities — the specialties at the top of this list are where a 1% rate concession moves the needle, and where we want to be sharpest before contracts open.
| 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 |
|---|---|---|
| provider_id | INT | PK |
| npi | TEXT | |
| specialty | TEXT | |
| network_status | TEXT | |
| tin | TEXT | |
| state | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two-table join: claims on the fact side, providers on the dimension side, joined on the provider key. The grouping attribute (specialty) lives only on the dimension — claims carry the provider_id, not the specialty.
Aggregate is a row count per specialty. Two pieces still need to fit on top of the GROUP BY: a way to rank the result so the largest counts read first, and a way to keep only the leaders rather than every specialty in the network.
Two output columns named for what they hold. When the brief asks for a 'top N', think about which clause cuts the result set vs. which clause orders it — they're not the same operation and the order they appear in matters.