578. Get Highest Answer Rate Question


Get the highest answer rate question from a table survey_log with these columns: uid, action, question_id, answer_id, q_num, timestamp.

uid means user id; action has these kind of values: "show", "answer", "skip"; answer_id is not null when action column is "answer", while is null for "show" and "skip"; q_num is the numeral order of the question in current session.

Write a sql query to identify the question which has the highest answer rate.

Example:

Input:
+------+-----------+--------------+------------+-----------+------------+
| uid  | action    | question_id  | answer_id  | q_num     | timestamp  |
+------+-----------+--------------+------------+-----------+------------+
| 5    | show      | 285          | null       | 1         | 123        |
| 5    | answer    | 285          | 124124     | 1         | 124        |
| 5    | show      | 369          | null       | 2         | 125        |
| 5    | skip      | 369          | null       | 2         | 126        |
+------+-----------+--------------+------------+-----------+------------+
Output:
+-------------+
| survey_log  |
+-------------+
|    285      |
+-------------+
Explanation:
question 285 has answer rate 1/1, while question 369 has 0/1 answer rate, so output 285.

Note: The highest answer rate meaning is: answer number's ratio in show number in the same question.


b'
\n
\n\n
\n

Solution

\n
\n

Approach I: Using sub-query and SUM() function [Accepted]

\n

Intuition

\n

Calculate the answered times / show times for each question.

\n

Algorithm

\n

First, we can use SUM() to get the total number of answered times as well as the show times for each question using a sub-query as below.

\n
SELECT\n    question_id,\n    SUM(CASE\n        WHEN action = \'answer\' THEN 1\n        ELSE 0\n    END) AS num_answer,\n    SUM(CASE\n        WHEN action = \'show\' THEN 1\n        ELSE 0\n    END) AS num_show\nFROM\n    survey_log\nGROUP BY question_id\n;\n
\n
| question_id | num_answer | num_show |\n|-------------|------------|----------|\n| 285         | 1          | 1        |\n| 369         | 0          | 1        |\n
\n

Then we can calculate the answer rate by its definition.

\n

MySQL

\n
SELECT question_id as survey_log\nFROM\n(\n    SELECT question_id,\n         SUM(case when action="answer" THEN 1 ELSE 0 END) as num_answer,\n        SUM(case when action="show" THEN 1 ELSE 0 END) as num_show,    \n    FROM survey_log\n    GROUP BY question_id\n) as tbl\nORDER BY (num_answer / num_show) DESC\nLIMIT 1\n
\n

Approach II: Using sub-query and COUNT(IF...) function [Accepted]

\n

Algorithm

\n

This solution is very straight forward: use the COUNT() function to sum the answer and show time combining with the IF() function.

\n

MySQL

\n
SELECT \n    question_id AS \'survey_log\'\nFROM\n    survey_log\nGROUP BY question_id\nORDER BY COUNT(answer_id) / COUNT(IF(action = \'show\', 1, 0)) DESC\nLIMIT 1;\n
\n
'