Transform survey responses

[ybyzek]

Consider a situation where you have raw survey data, which you can now analyze.

Sample query to draw from and adapt to ksql, and then add a join to join the survey responses onto CRM data to see if participants map to any existing accounts. (this code sample is not ksql-ready, it needs to be adapted)

SELECT
   programming_answers.Language  AS programming_answers_language,
   project_answers.Title  AS project_answers_title,
   AVG((case when programming_answers.Level='None' then 0 
when programming_answers.Level='beginner' then 1
when programming_answers.Level='competent' then 2 
when programming_answers.Level='proficient' then 3
when programming_answers.Level='expert' then 4 
else null end) ) AS programming_answers_average_level_value
FROM `my-project.DesignPattern.A000111_ProjectAnswers` AS project_answers
INNER JOIN `my-project.A000111_ProgrammingAnswers` AS programming_answers
ON programming_answers.RESPONSE_ID = project_answers.RESPONSE_ID
GROUP BY 1,2
ORDER BY 3 DESC

Inspiration: https://codelabs.developers.google.com/codelabs/survey-data-warehouse#6