Answer detail

1-- core.typeform.answer_detail
2-- Every answer to every question, in long format, with choice answers unnested.
3-- Grain: typeform_account x response x question x selected choice. A non-choice
4-- answer is one row with NULL choice columns; a multi-select answer is one row per
5-- option ticked.
6-- Depends on: staging.typeform.response, .response_answer, .response_answer_choice,
7--             .form_field, .form
8--
9-- This is the model for answer distributions: count rows per `answer_value` for a
10-- question and you have Typeform's per-question summary. For a multi-select
11-- question the counts add up to more than the number of respondents, by design;
12-- divide by `COUNT(DISTINCT response_token)` for "share of respondents who
13-- picked this option", which is what Typeform's Results page shows.
14
15SELECT
16    a.typeform_account,
17    a.form_id,
18    f.form_title,
19    a.response_token,
20    r.submitted_at,
21    DATE(r.submitted_at) AS submitted_date,
22
23    a.field_id,
24    -- Falls back to the answer's own ref when the question has since been deleted
25    -- from the form and has no row in form_field any more.
26    COALESCE(q.field_title, a.field_ref) AS question,
27    COALESCE(q.field_type, a.field_type) AS field_type,
28    q.parent_field_id,
29    a.answer_type,
30
31    c.choice_id,
32    c.choice_text,
33    -- One display value per row, whatever the answer type, for grouping in BI.
34    COALESCE(
35        c.choice_text,
36        a.answer_text,
37        a.answer_email,
38        a.answer_phone_number,
39        a.answer_file_url,
40        CAST(a.answer_number AS STRING),