## 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

## 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
'