First aggregation on fact_calls. Rep call activity is the field-force productivity headline; sales managers run this view every Monday to spot under-performers and over-performers. Two-table join, GROUP BY on the dimension's name column.
Monday morning rep activity — Marcus is reviewing the field force at 11. Give me total call counts per rep over the full 24-month window, joined to dim_rep so we get the rep_name on the page (not just rep_id). Two columns: rep_name and call_count, sorted by call_count descending so the top callers lead. We're going to spend most of the meeting on the bottom of the list — anyone under 100 calls in 24 months is a coaching conversation.
| Column | Type | Key |
|---|---|---|
| call_id | INT | PK |
| hcp_npi | TEXT | FK → dim_hcp |
| rep_id | INT | FK → dim_rep |
| product_id | INT | FK → dim_product |
| call_date | TEXT | |
| duration_min | INT | |
| samples_dropped | INT |
| Column | Type | Key |
|---|---|---|
| rep_id | INT | PK |
| rep_name | TEXT | |
| manager_name | TEXT | |
| territory_id | INT | FK → dim_geography |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two-table join: fact_calls to dim_rep on rep_id. Group by the rep's human-readable name so the report header is meaningful.
COUNT(*) aliased as call_count. No date filter — Marcus wants the full 24-month window for the activity baseline.
Descending sort on the call_count surfaces the top performers up top so managers can scan past them quickly to the bottom — that's where the meeting will focus.