Get Highest Answer Rate Question
Problem
SurveyLog(id, action ∈ {show,answer,skip}, question_id, answer_id, q_num, timestamp). Return the question_id with the highest rate = answers / shows, breaking ties by smallest id.
logs=[(5,'show',285,null,1,123),(5,'answer',285,124124,1,124),(5,'show',369,null,2,125),(5,'skip',369,null,2,126)]285SELECT question_id AS survey_log
FROM SurveyLog
GROUP BY question_id
ORDER BY SUM(action='answer') / SUM(action='show') DESC, question_id ASC
LIMIT 1;Explanation
For each question we want its answer rate — how often a shown question got answered — and then the single question with the highest rate. We compute this per question by grouping.
GROUP BY question_id gathers every log line for a question. Inside each group, the expression SUM(action='answer') counts the answers and SUM(action='show') counts the shows. (In MySQL a comparison like action='answer' evaluates to 1 or 0, so summing it is just counting.)
Dividing answers by shows gives the rate, and we ORDER BY that rate descending so the best question floats to the top. The tiebreaker question_id ASC picks the smallest id when rates are equal, and LIMIT 1 returns just the winner.
Example: question 285 was shown once and answered once, a rate of 1.0; question 369 was shown once but only skipped, a rate of 0.0. So 285 wins.