First LEFT JOIN with COALESCE — surface dimension rows that have zero matching fact rows. The 'zero-activity rep' problem is the canonical anti-pattern detection in pharma sales ops; a rep who has zero logged calls in a quarter is a flag-on-the-play, not 'no data'.
Looking ahead to next quarter's QBR — Marcus wants any rep at risk of zero recorded activity in a window flagged automatically. For now, give me each rep's full-window call count, but use a LEFT JOIN from dim_rep to fact_calls so a hypothetical zero-call rep would surface as 0 (not silently disappear). Three output columns: rep_name, manager_name, call_count. Sort by call_count ASC so the lowest activity comes first — that's the meeting agenda. We don't actually have any zero-call reps this window (every rep made at least 70 calls), but the LEFT JOIN guards against the silent-drop bug for next quarter.
| Column | Type | Key |
|---|---|---|
| rep_id | INT | PK |
| rep_name | TEXT | |
| manager_name | TEXT | |
| territory_id | INT | FK → dim_geography |
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
LEFT JOIN dim_rep TO fact_calls (NOT INNER JOIN — INNER would silently drop a zero-call rep). The LEFT preserves every dim_rep row even when no fact_calls match.
COUNT(fc.call_id), not COUNT(*). COUNT(*) counts the NULL-padded LEFT JOIN row as 1 for a zero-call rep (silent inflation); COUNT(fc.call_id) correctly returns 0 for that rep because COUNT skips NULLs.
Three output columns: rep_name, manager_name, the COUNT(fc.call_id) aliased as call_count. Sort ascending on call_count so the lowest activity comes first — that's the meeting agenda. No COALESCE needed when COUNT correctly returns 0 for empty groups.