First step into Medium tier — 2-table INNER JOIN + GROUP BY + ORDER BY DESC + LIMIT. The canonical 'top N by frequency' shape that every brand-team weekly call opens with. The on-ramp from Easy aggregations to the decile / RANK / NTILE patterns in Hard tier (M11-M14).
Brand team standup at 9:30 — I want our top 10 highest-volume prescribers across the full 24-month window in front of the team. Pull from fact_rx joined to dim_hcp; output hcp_name, specialty, and rx_count. Sort by rx_count descending, alphabetic on hcp_name as a stable tiebreaker (we'll see ties at this volume range — the seed has a few HCPs clustered tightly at the top). Top 10 only. This is the call list we're going to pressure-test against the field-force coverage plan.
| 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 |
|---|---|---|
| npi | TEXT | PK |
| hcp_name | TEXT | |
| specialty | TEXT | |
| decile | INT | |
| is_target | INT | |
| territory_id | INT | FK → dim_geography |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two-table INNER JOIN: fact_rx to dim_hcp on hcp_npi. Group by the HCP's npi (or hcp_name + specialty); count rows. Two output columns from dim_hcp + the count.
GROUP BY h.npi (the PK) is safest if your SQL dialect requires it; SELECT can still surface hcp_name and specialty from the same row. COUNT(*) AS rx_count.
Top-N pattern: descending sort on the count, alphabetic tiebreaker on hcp_name (load-bearing — without it the top 10 shuffles between reruns when HCPs tie at the top), then cap with LIMIT.