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:
|
|
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.