Get Highest Answer Rate Question

You can calculate the answer rate for each question by using the SUM and COUNT functions to determine how many times each question was answered and showed. Then, you can use the ORDER BY and LIMIT clauses to select the question with the highest answer rate and the smallest question_id.

Here’s the query:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT question_id AS survey_log
FROM (
  SELECT question_id,
         SUM(CASE WHEN action = 'answer' THEN 1 ELSE 0 END) / COUNT(CASE WHEN action = 'show' THEN 1 END) AS answer_rate
  FROM SurveyLog
  WHERE action IN ('show', 'answer')
  GROUP BY question_id
) AS rate_table
ORDER BY answer_rate DESC, question_id ASC
LIMIT 1;

Explanation:

  • The inner subquery calculates the answer rate for each question by dividing the number of ‘answer’ actions by the number of ‘show’ actions.
  • The WHERE clause filters only ‘show’ and ‘answer’ actions, as we are interested in calculating the answer rate based on these actions.
  • The outer query orders the results by the answer rate in descending order and then by the question_id in ascending order.
  • The LIMIT 1 clause ensures that only the question with the highest answer rate and the smallest question_id is returned.