Topic volume

1-- core.medallia.topic_volume
2-- How often each text analytics topic comes up in comments, per unit per month.
3-- Grain: month x medallia_account x unit x topic. Months are UTC calendar months
4-- of the record's `responded_at`.
5-- Depends on: staging.medallia.feedback_topic, .feedback, .unit
6--
7-- `feedback_records` counts records with at least one comment tagged with the
8-- topic, so a record that mentions "wait time" in two comments counts once.
9-- `share_of_commenting_records` divides by records in the same unit and month
10-- that carry any tagged comment, the usual "% of comments mentioning" figure.
11-- Both are distinct counts: sum neither across months or units; recount instead.
12
13WITH tagged AS (
14    SELECT DISTINCT
15        t.medallia_account,
16        DATE_TRUNC(DATE(f.responded_at), MONTH) AS month,
17        f.unit_identifier,
18        t.topic_id,
19        t.topic_name,
20        t.feedback_id
21    FROM {{staging.medallia.feedback_topic}} t
22    INNER JOIN {{staging.medallia.feedback}} f
23            ON f.medallia_account = t.medallia_account
24           AND f.feedback_id      = t.feedback_id
25    WHERE f.responded_at IS NOT NULL
26),
27
28commenting AS (
29    SELECT
30        medallia_account,
31        month,
32        unit_identifier,
33        COUNT(DISTINCT feedback_id) AS commenting_records
34    FROM tagged
35    GROUP BY 1, 2, 3
36),
37
38topics AS (
39    SELECT
40        medallia_account,