Response volume

1-- core.zigpoll.response_volume
2-- Answers and respondents per poll per day.
3-- Grain: day x zigpoll_account x poll. Dates are UTC days of the response's
4-- `created_at`. Synthetic polls are excluded.
5-- Depends on: staging.zigpoll.response, .poll
6--
7-- Zigpoll stores one response per participant per question, so there are two
8-- different "how many" numbers and both are returned:
9--   answers      every response row, i.e. questions answered;
10--   respondents  distinct participants who answered at least one question.
11-- `respondents` is a distinct count. It cannot be summed across days: a person
12-- answering on Monday and Tuesday is one respondent for the week, two in the sum.
13-- Recount from answer_detail for any period longer than a day.
14--
15-- Built on a day spine so a quiet day returns zeros instead of disappearing.
16--
17-- Poll views, opens and completions exist only as Zigpoll's lifetime counters on
18-- the poll (staging.zigpoll.poll), with no dates, so there is no daily
19-- participation rate. Divide totals against those counters at poll level instead.
20
21WITH responses AS (
22    SELECT
23        r.zigpoll_account,
24        r.poll_id,
25        DATE(r.created_at) AS response_date,
26        r.response_id,
27        r.participant_id,
28        r.completed_poll
29    FROM {{staging.zigpoll.response}} r
30    INNER JOIN {{staging.zigpoll.poll}} p
31            ON p.zigpoll_account = r.zigpoll_account
32           AND p.poll_id         = r.poll_id
33    WHERE NOT p.is_synthetic
34      AND r.created_at IS NOT NULL
35),
36
37day_spine AS (
38    SELECT day
39    FROM UNNEST(GENERATE_DATE_ARRAY(
40        (SELECT MIN(response_date) FROM responses),