First aggregation on fact_rx joined to dim_product. Per-product Rx volume is the headline KPI on every brand-team scorecard — every Hard / Expert / Master mission downstream extends this skeleton.
Standing brand-team scorecard — I need the headline number for tomorrow's Tuesday review. Across the full 24-month window in fact_rx, give me the total fill count per product. Use brand_name from dim_product so the slide reads cleanly (not product_id). Two columns: brand_name and rx_count. Sort by rx_count descending — incumbents on top, Cardiozin should land mid-pack since it's only been on market 8 months. Don't filter on date or anything else; we want the full window.
| 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 |
|---|---|---|
| product_id | INT | PK |
| brand_name | TEXT | |
| generic_name | TEXT | |
| therapeutic_class | TEXT | |
| is_launch_brand | INT | |
| launch_date | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two-table aggregate. You'll need fact_rx (the volume) joined to dim_product (the readable name), grouped by the brand-name column.
Output column name matters — alias COUNT(*) as rx_count so the scorecard slide binds correctly. Two columns total.
ORDER BY rx_count DESC. Cardiozin's launch was 2025-09-01, so it has only ~8 months of fills against incumbents' 24 — expect it mid-pack, not first.