Get Highest Answer Rate Question - Leetcode Solution

Problem Statement:

You are given a table SurveyLog with these columns:

• uid: A string representing Anonymous id (ANONYMOUS_UID) of the user who visited the survey page.

• action: A string representing the action taken by the user which could be one of these:

  • "show" representing that the user visited the survey page.
  • "answer" representing that the user answered a question belonging to the survey with id survey_id.

• survey_id: A string representing the id of the survey.

• question_id: A string representing the id of the question.

• answer_id: A string representing the id of the answer if the action taken by the user was "answer". If the action was show, this column will be null.

• timestamp: A string representing the timestamp of the action taken.

Note that same survey could be visited multiple times and that the uid is anonymous.

Write an SQL query to identify the survey_id with the highest answer rate.

• answer rate: The ratio of the number of users answered a question to the number of users visited the survey page.

Return the survey_id and it's answer rate with no leading zeros. Round answer rate to any scale you need to.

Example 1:

Input:

SurveyLog table:
+------+--------+------------+--------------+------------+-------------+
| uid  | action | survey_id  | question_id  | answer_id  | timestamp   |
+------+--------+------------+--------------+------------+-------------+
| id1  | show   | SURVEY1    | Q1           | null       | 2016-06-01  |
| id1  | answer | SURVEY1    | Q1           | ANSWER1    | 2016-06-01  |
| id2  | show   | SURVEY1    | Q1           | null       | 2016-06-01  |
| id2  | answer | SURVEY1    | Q2           | ANSWER2    | 2016-06-01  |
| id3  | show   | SURVEY2    | Q1           | null       | 2016-06-01  |
| id3  | answer | SURVEY2    | Q1           | ANSWER3    | 2016-06-01  |
| id4  | show   | SURVEY2    | Q1           | null       | 2016-06-01  |
| id4  | answer | SURVEY2    | Q1           | ANSWER4    | 2016-06-01  |
+------+--------+------------+--------------+------------+-------------+

Output:

Result table:
+----------+-------------------+
| survey_id|    `answer_rate`  |
+----------+-------------------+
| SURVEY1  |         0.5       |
| SURVEY2  |         1.0       |
+----------+-------------------+

Example Explanation:

• In Survey1, Total visitors are 2 and Total answers are 1. Answer rate is 1/2 = 0.5 (50%).
• In Survey2, Total visitors are 2 and Total answers are 2. Answer rate is 2/2 = 1.0 (100%).

An SQL query that should solve this problem is:

SELECT survey_id, ROUND(SUM(CASE WHEN action = "answer" THEN 1 ELSE 0 END) / COUNT(DISTINCT uid), 2) AS `answer_rate` 
FROM SurveyLog 
GROUP BY survey_id 
ORDER BY `answer_rate` DESC 
LIMIT 1;

Explanation:

The query uses an aggregate function, SUM and COUNT, with GROUP BY.

• SUM(CASE WHEN action = "answer" THEN 1 ELSE 0 END) calculates the total number of users who answered a question of this survey.

• COUNT(DISTINCT uid) calculates the total number of distinct users who visited this survey.

• ROUND(TRUNCATE(SUM(CASE WHEN action = "answer" THEN 1 ELSE 0 END) / COUNT(DISTINCT uid), 2), 2) calculates the answer rate by dividing the two above values, then rounding the result to two decimal places.

• ORDER BY answer_rate DESC LIMIT 1 orders the result in descending order of answer_rate and returns only the first row (highest answer_rate).

1