Date-string slicing with substr() to roll fact_calls up to quarterly buckets. Pharma stores dates as YYYY-MM-DD TEXT; carving out year + quarter is the most common reporting transform. Single-table aggregate with a derived column — a Medium step up from M4's flat per-rep count.
Marcus wants the field-force activity trend for the QBR deck. From fact_calls, give me total call counts rolled to the quarter — output 'YYYY-Qn' format (e.g. '2025-Q3'), sorted ascending so the trend line reads left-to-right. Two columns: quarter, calls. Use substr() to slice the call_date string; the math for 'which quarter' is (month + 2) / 3, integer division. The 24-month window has 9 quarters; expect 2024-Q2 through 2026-Q2.
| 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.
substr(call_date, 1, 4) extracts the year (positions 1-4); substr(call_date, 6, 2) extracts the month (positions 6-7). Both come back as TEXT — cast the month to INTEGER before doing math on it.
Quarter math is integer division on the month number — once you have month as an INTEGER, you can map (month → 1/2/3/4) with a single arithmetic expression. Concat with the year via || and a literal '-Q' separator to get the '2025-Q3' shape.
GROUP BY the derived quarter expression. COUNT(*) AS calls. ORDER BY quarter ASC so the trend reads left-to-right.