Rep call activity by day

1-- core.gong.rep_call_activity_by_day
2-- Calls per rep per day: how many, how long, external versus internal, inbound
3-- versus outbound. The activity half of Gong's Team Stats.
4--
5-- Grain: day x gong_account x rep (primary user).
6-- Depends on: core.gong.calls, staging.gong.user
7--
8-- A call counts for its primary user only. A call with two reps on it is in one
9-- rep's row, so summing across reps gives the true call count.
10--
11-- TIME ZONE: days are cut in UTC. Change the 'UTC' literal below to your reporting
12-- zone if late-afternoon calls in the Americas land on the next day.
13--
14-- Built on a spine of every rep who is active or has ever owned a call x every day
15-- from the first call to today, so a rep's quiet day is a row of zeros rather than
16-- a gap a line chart would interpolate over.
17
18WITH params AS (
19    SELECT 'UTC' AS tz
20),
21
22daily AS (
23    SELECT
24        c.gong_account,
25        COALESCE(c.primary_user_id, 'unknown')                    AS user_id,
26        DATE(c.started_at, p.tz)                                  AS date,
27        COUNT(*)                                                  AS calls,
28        COUNTIF(c.scope = 'External')                             AS external_calls,
29        COUNTIF(c.scope = 'Internal')                             AS internal_calls,
30        COUNTIF(c.direction = 'Inbound')                          AS inbound_calls,
31        COUNTIF(c.direction = 'Outbound')                         AS outbound_calls,
32        COUNTIF(c.direction = 'Conference')                       AS conference_calls,
33        SUM(COALESCE(c.duration_seconds, 0)) / 60.0               AS call_minutes,
34        SUM(IF(c.scope = 'External', COALESCE(c.duration_seconds, 0), 0)) / 60.0
35                                                                  AS external_call_minutes,
36        COUNTIF(c.crm_deal_ids IS NOT NULL)                       AS calls_linked_to_deal
37    FROM {{core.gong.calls}} c
38    CROSS JOIN params p
39    WHERE c.started_at IS NOT NULL
40    GROUP BY 1, 2, 3