Discourse Surveys

Customization Plugin
, ,
21

I found that the SQL above did pull out the results, but it wasn’t in an easy to work with format; basically it would require a tonne of transformation at the spreadsheet level to enable useful analysis.

However, with a bit of Gen AI help, I was able to produce a very sweet Data Explorer query. Due to some limitations of Data Explorer, it isn’t possible to make it dynamic enough to cope with a variety of surveys; basically you need a bespoke query per survey.

Fortunately, Gen AI can make that pretty straight forward. Here is a sample prompt that will probably work in most Gen AI services (if you don’t have the AI plugin active) - just plugin in your survey markdown and you should be away:

Gen AI Prompt to Produce Tailored SQL Query for a Specific Survey

I have a survey defined in markdown format, and I need a SQL query to extract the survey results from a Discourse database using the Data Explorer Plugin. The survey includes various types of questions such as radio, dropdown, star, checkbox, textarea, thumbs, and number. I want the query to output results with each user having a single row and each question having its own column. For checkbox questions, multiple responses should be concatenated into a single string.

Here is the survey markdown:

Paste your survey markdown here

Here is the original query used to obtain survey results:

-- [params]
-- text :survey_name = survey

SELECT s.id, s.name, s.post_id, sf.question, COALESCE(sr.value, sfo.html) AS value, sr.user_id, sr.created_at as responded_at
FROM surveys s
JOIN survey_fields sf ON sf.survey_id = s.id
JOIN survey_responses sr ON sr.survey_field_id = sf.id
LEFT JOIN survey_field_options sfo ON sfo.id = sr.survey_field_option_id
WHERE s.name = :survey_name
ORDER BY s.id DESC

Please generate a SQL query that:

  • Hardcodes the survey name.
  • Uses conditional aggregation to pivot the data, with each question as a column.
  • Utilizes STRING_AGG for checkbox questions to handle multiple responses.
  • Ensures the output is grouped by user_id and ordered by user_id.
  • Is formatted for readability and includes comments explaining the logic where necessary.

Additional Guidance for Data Explorer Plugin:

  • The query should be compatible with the Discourse Data Explorer Plugin, which means it should not end with a semicolon.
  • Use column names like user_id to leverage automatic linking features within the Data Explorer.
  • Avoid using dynamic SQL, as the plugin does not support it.
  • Ensure the query is efficient and optimized for performance, as it may be run on large datasets.
9 Likes