> For the complete documentation index, see [llms.txt](https://lei-d.gitbook.io/sql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://lei-d.gitbook.io/sql/leetcode/578get-highest-answer-rate-question.md).

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

## Solution 1

```php
SELECT question_id AS survey_log
FROM (
        SELECT question_id,
                SUM(IF(action = 'show', 1, 0)) AS show_num,
                SUM(IF(action = 'answer', 1, 0)) AS answer_num
        FROM survey_log
        GROUP BY question_id
        ) AS temp
ORDER BY IFNULL((answer_num / show_num), 1) DESC, answer_num DESC
LIMIT 1;
```

## Solution 2

```php
SELECT question_id as survey_log
FROM survey_log
GROUP BY question_id
ORDER BY avg(case when action = 'answer' then 1 else 0 end) desc
LIMIT 1;
```