Answer detail

1-- core.zigpoll.answer_detail
2-- Every answer, in long format, with selected options unnested.
3-- Grain: zigpoll_account x response x selected option (x matrix row). A response
4-- with no option rows (open text, email capture, slider) is one row with NULL
5-- option columns and its text in `response_text`.
6-- Depends on: staging.zigpoll.response, .response_answer, .slide, .poll
7--
8-- For an answer distribution, count rows per `answer_value` for one slide. On a
9-- multi-select slide the counts add up to more than the number of respondents;
10-- divide by COUNT(DISTINCT participant_id) for the share of respondents who picked
11-- each option. Do not group on `response_text` for that: it is the compiled
12-- display string, so "A, B" and "A" are different values.
13
14SELECT
15    r.zigpoll_account,
16    r.poll_id,
17    p.poll_title,
18    p.is_synthetic,
19    r.slide_id,
20    s.slide_title,
21    s.slide_type,
22    r.response_id,
23    r.participant_id,
24    r.created_at,
25    DATE(r.created_at) AS response_date,
26    r.completed_poll,
27
28    a.answer_handle,
29    a.answer_title,
30    a.row_handle,
31    a.row_title,
32    a.answer_rank,
33    r.response_text,
34    -- One display value per row: the option when there is one, the text otherwise.
35    COALESCE(a.answer_title, r.response_text) AS answer_value,
36
37    -- Order context. Repeated on every option row of a multi-select answer, so sum
38    -- it per distinct shopify_order_id, never per row (see attribution_by_answer).
39    r.shopify_order_id,
40    r.order_value,