Two-table dim-to-dim INNER JOIN: dim_hcp to dim_geography on territory_id. Practice the territory-level rollup — the cornerstone view for district sales managers reviewing field-force coverage. No facts involved; just dimension-to-dimension navigation.
District planning meeting Thursday — I need an HCP-density view by territory. From dim_hcp joined to dim_geography on territory_id, give me HCP count per territory. Three output columns: territory_name, region, hcp_count. Sort by hcp_count descending, alphabetic on territory_name as the tiebreaker. Heads up — a couple of launch-priority territories are deliberately oversized (the launch team built up coverage ahead of Cardiozin), so expect a handful of territories to pop noticeably above the rest.
| Column | Type | Key |
|---|---|---|
| npi | TEXT | PK |
| hcp_name | TEXT | |
| specialty | TEXT | |
| decile | INT | |
| is_target | INT | |
| territory_id | INT | FK → dim_geography |
| Column | Type | Key |
|---|---|---|
| territory_id | INT | PK |
| territory_name | TEXT | |
| region | TEXT | |
| area | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two-table INNER JOIN: dim_hcp to dim_geography on territory_id. Group by the territory's name (and region, since region is in the SELECT and not aggregated).
Three output columns: territory_name, region, hcp_count = COUNT(*). One row per territory.
Two-level sort: hcp_count descending so the oversized launch territories surface at the top, alphabetic tiebreaker on territory_name to keep the order stable when territories tie.