578 Get Highest Answer Rate Question

Description

Table: SurveyLog

• This table may contain duplicate rows.
• action is an ENUM (category) of the type: “show”, “answer”, or “skip”.
• Each row of this table indicates the user with ID = id has taken an action with the question question_id at time timestamp.
• If the action taken by the user is “answer”, answer_id will contain the id of that answer, otherwise, it will be null.
• q_num is the numeral order of the question in the current session.

The answer rate for a question is the number of times a user answered the question by the number of times a user showed the question.

Problem Statement

Write a solution to report the question that has the highest answer rate. If multiple questions have the same maximum answer rate, report the question with the smallest question_id.

The result format is in the following example.

Example 1:

Input:

• SurveyLog table:

Output:

Explanation:

• Question 285 was showed 1 time and answered 1 time. The answer rate of question 285 is 1.0
• Question 369 was showed 1 time and was not answered. The answer rate of question 369 is 0.0
• Question 285 has the highest answer rate.

Solution

In this problem, we essentially need the count of times a question_id was shown and the count of times the question was answered. Finally, we need to divide the number of times it was answered by the number of times it was shown. This will provide us the answer rate.

Approach 1: Using Subquery

1. Find the number of times a question was shown and the number of times it was answered.

1SELECT question_id,
2    SUM(CASE WHEN action = 'show' THEN 1 ELSE 0 END) AS times_shown,
3    SUM(CASE WHEN action = 'answer' THEN 1 ELSE 0 END) AS times_answered
4    FROM SurveyLog
5    GROUP BY question_id

2. Next, you need to order the results by the answer rate in descending order and then by question id in ascending order. You also want to limit the result to just one record.

 1WITH question_stats AS (
 2    SELECT question_id,
 3        SUM(CASE WHEN action = 'show' THEN 1 ELSE 0 END) AS times_shown,
 4        SUM(CASE WHEN action = 'answer' THEN 1 ELSE 0 END) AS times_answered
 5        FROM SurveyLog
 6        GROUP BY question_id
 7) SELECT question_id AS survey_log
 8    FROM question_stats
 9    ORDER BY times_answered / times_shown DESC, question_id ASC
10    LIMIT 1;

Using Aggregation in ORDER BY

In above solution, I used a subquery to get the answer rate for each question. However, this can be done in single query by ordering the results using aggregation.

1SELECT question_id AS survey_log
2    FROM SurveyLog
3    GROUP BY question_id
4    ORDER BY (
5        SUM(CASE WHEN action = 'answer' THEN 1 ELSE 0 END) / 
6        SUM(CASE WHEN action = 'show' THEN 1 ELSE 0 END)
7    ) DESC, question_id ASC
8    LIMIT 1;