Answer detail

1-- core.qualtrics.answer_detail
2-- Every answer to every question, in long format, with question and matrix-row
3-- text attached.
4-- Grain: qualtrics_account x response x question x sub question x response_index.
5-- A single-answer question is one row; a multi-select answer is one row per choice
6-- selected; a matrix answer is one row per matrix row (sub question).
7-- Depends on: staging.qualtrics.question_response, .survey_response, .question,
8--             .sub_question, .survey
9--
10-- Test, preview and spam responses are kept but flagged with `is_test_or_spam`;
11-- filter on it for anything that should match Qualtrics' reports.
12--
13-- For an answer distribution, count rows per `response_label` for one question
14-- (and one `sub_question_key` on a matrix). For a multi-select question the counts
15-- add up to more than the number of respondents; divide by
16-- COUNT(DISTINCT response_id) for the share of respondents choosing each option.
17
18SELECT
19    a.qualtrics_account,
20    r.survey_id,
21    s.survey_name,
22    a.response_id,
23    r.recorded_at,
24    DATE(r.recorded_at) AS recorded_date,
25    r.is_finished,
26    r.is_test_or_spam,
27    r.distribution_channel,
28
29    a.question_id,
30    q.question_name,
31    q.question_text,
32    q.question_type,
33    q.selector,
34    a.sub_question_key,
35    sq.sub_question_text,
36    a.response_index,
37
38    -- The exported value (a choice's recode, or the typed text) and the label the
39    -- respondent saw. Use the label for display and the value for arithmetic.
40    a.response_value,