First two-table join: fact_rx to dim_payer_plan on plan_id. Payer mix (commercial vs Medicare vs Medicaid) is the headline shape behind every market access strategy and gross-to-net forecast. INNER JOIN on a fact, GROUP BY on a dim attribute.
Market access steering at 3pm needs a clean payer-mix read. From fact_rx joined to dim_payer_plan, give me Rx count grouped by plan_type — Commercial, Medicare D, Medicaid, and Cash should all show. Two columns: plan_type, rx_count. Sort by rx_count descending so commercial leads (it always does). Note: a small number of fact_rx rows have NULL plan_id from the documentation gap I'm flagging in this week's data-quality review — INNER JOIN drops those automatically, which is what we want here.
| Column | Type | Key |
|---|---|---|
| rx_id | INT | PK |
| hcp_npi | TEXT | FK → dim_hcp |
| product_id | INT | FK → dim_product |
| plan_id | INT | FK → dim_payer_plan |
| fill_date | TEXT | |
| is_new_rx | INT | |
| days_supply | INT | |
| quantity | INT | |
| copay | REAL |
| Column | Type | Key |
|---|---|---|
| plan_id | INT | PK |
| plan_name | TEXT | |
| plan_type | TEXT | |
| formulary_tier | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two-table INNER JOIN: fact_rx to dim_payer_plan on plan_id. The INNER JOIN handles the NULL-plan_id rows automatically — they get dropped, which is what Lin wants.
Group by the dim's plan_type attribute and count. Two-column output.
Sort descending on the count so commercial leads — that's where the steering meeting builds its narrative.