Scorecard scores by rep

1-- core.gong.scorecard_scores_by_rep
2-- Coaching scorecards per rep per month: how many calls were reviewed, by how many
3-- reviewers, and the average scores the rep received.
4--
5-- Grain: review month x gong_account x reviewed rep x scorecard.
6-- Depends on: staging.gong.answered_scorecard, .answered_scorecard_answer,
7--             .scorecard_question, .user
8--
9-- SCORES ARE PUT ON 0 TO 1 before averaging. Gong scorecards let each scale
10-- question have its own range (1 to 5, 1 to 10), so averaging raw scores across
11-- questions or scorecards mixes scales. normalised = (score - min) / (max - min),
12-- using the range of the question revision the answer was given on. Raw averages
13-- are kept alongside for matching the Gong UI on a single scorecard.
14--
15-- not_applicable answers and answers without a numeric score (free text, yes/no,
16-- multiple choice) are excluded from every average.
17--
18-- Months are by review date, not call date: coaching activity happens when the
19-- manager reviews, which can be weeks after the call.
20
21WITH answer AS (
22    SELECT
23        s.gong_account,
24        s.answered_scorecard_id,
25        s.scorecard_id,
26        s.scorecard_name,
27        s.reviewed_user_id,
28        s.reviewer_user_id,
29        s.reviewed_at,
30        a.is_overall,
31        a.score,
32        SAFE_DIVIDE(a.score - q.min_range, q.max_range - q.min_range) AS normalised_score
33    FROM {{staging.gong.answered_scorecard}} s
34    JOIN {{staging.gong.answered_scorecard_answer}} a
35        ON  a.gong_account          = s.gong_account
36        AND a.answered_scorecard_id = s.answered_scorecard_id
37    LEFT JOIN {{staging.gong.scorecard_question}} q
38        ON  q.gong_account         = a.gong_account
39        AND q.scorecard_id         = s.scorecard_id
40        AND q.question_id          = a.question_id