Rep interaction stats by week

1-- core.gong.rep_interaction_stats_by_week
2-- Gong's Interaction stats per rep per week: talk ratio, longest monologue,
3-- longest customer story, interactivity, patience and question rate.
4--
5-- Grain: ISO week (starting Monday) x gong_account x rep (primary user).
6-- Depends on: core.gong.calls
7--
8-- EXTERNAL CALLS ONLY. Interaction stats are a coaching measure for conversations
9-- with customers and prospects; an internal stand-up would drag every rep's talk
10-- ratio toward 50%. Remove the scope filter to include them.
11--
12-- AVERAGE OF PER-CALL VALUES, ON PURPOSE. Gong's help centre defines a rep's
13-- average talk ratio as the sum of their per-call talk ratios divided by the number
14-- of calls, so a 10-minute call weighs the same as a 60-minute one. The avg_gong_*
15-- columns follow that definition so they can be checked against the Gong UI.
16-- talk_time_weighted_rep_talk_ratio is the other reading (total rep seconds over
17-- total seconds), which long calls dominate. They disagree whenever call lengths
18-- vary; neither is a bug.
19--
20-- To re-aggregate to month or team, sum the *_sum and *_calls columns and divide.
21-- Averaging the avg_ columns gives a mean of means.
22--
23-- No spine: a week with no external calls has no stats to average, and a zero
24-- would be read as a talk ratio of 0%.
25
26SELECT
27    DATE_TRUNC(call_date, ISOWEEK)                                AS week_start,
28    gong_account,
29    primary_user_id                                               AS user_id,
30    ANY_VALUE(rep_name)                                           AS user_name,
31    ANY_VALUE(rep_manager_name)                                   AS manager_name,
32
33    COUNT(*)                                                      AS external_calls,
34    SUM(duration_minutes)                                         AS external_call_minutes,
35
36    -- Gong's own statistics, averaged per call.
37    COUNT(gong_talk_ratio)                                        AS gong_talk_ratio_calls,
38    SUM(gong_talk_ratio)                                          AS gong_talk_ratio_sum,
39    AVG(gong_talk_ratio)                                          AS avg_gong_talk_ratio,
40    AVG(gong_longest_monologue)                                   AS avg_gong_longest_monologue,