Skip to main content
Talentd

Get Highest Answer Rate Question - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: Meta
Practice this problem

Problem Statement

Table: SurveyLog

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| action      | ENUM |
| question_id | int  |
| answer_id   | int  |
| q_num       | int  |
| timestamp   | int  |
+-------------+------+
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.

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:
+----+--------+-------------+-----------+-------+-----------+
| id | 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 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.

Approach Overview

Problem Overview: A survey system logs events such as show, answer, and skip for each question. The task is to return the question_id with the highest answer rate, defined as number_of_answers / number_of_shows. Only one question ID should be returned—the one with the maximum computed rate.

Approach 1: Conditional Aggregation with GROUP BY (O(n) time, O(k) space)

The efficient solution scans the table once and groups rows by question_id. For each group, compute two counts: how many times the question was shown and how many times it was answered. In MySQL, conditional aggregation makes this easy by using expressions like SUM(action = 'answer') and SUM(action = 'show'). These boolean comparisons evaluate to 1 when true and 0 otherwise, effectively counting occurrences during aggregation.

Once both counts are available, compute the answer rate directly inside the query: SUM(action='answer') / SUM(action='show'). Sorting the grouped results by this ratio in descending order reveals the highest answer rate. Applying ORDER BY rate DESC followed by LIMIT 1 returns the desired question_id. This approach performs a single table scan and leverages SQL aggregation efficiently.

The key insight is that you do not need multiple passes or joins. SQL can compute conditional counts during the GROUP BY stage. This pattern shows up frequently in analytics-style database problems where metrics are derived from event logs. Understanding conditional aggregation is essential when working with database queries and metrics pipelines.

If the dataset contains many questions, the database engine maintains aggregation state per question. That requires memory proportional to the number of distinct question_id values. In practice this is small compared to the total number of log rows.

This problem is also a good demonstration of how event logs translate into metrics. Systems often record events such as impressions and actions, and metrics like click-through or answer rates are derived with aggregation queries. Practicing queries like this improves your fluency with SQL analytics and GROUP BY operations.

Recommended for interviews: The conditional aggregation approach is the expected solution. Interviewers want to see that you can compute multiple metrics from the same grouped dataset and derive ratios directly in SQL. Brute-force or multi-query solutions demonstrate basic understanding, but a single GROUP BY query with conditional counts shows strong SQL fluency.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Conditional Aggregation with GROUP BYO(n)O(k)Best general solution. Computes show and answer counts per question in one pass.
Subquery with Precomputed CountsO(n)O(k)Useful if answer and show counts are calculated separately and then joined.
Window Function RankingO(n log k)O(k)When ranking metrics across groups using ROW_NUMBER() or RANK().

Video Solution

LeetCode Medium 578 "Highest Answer Rate Question" Facebook/Meta Interview SQL Question Explanation • Everyday Data Science • 2,276 views views

Watch 3 more video solutions →

Frequently Asked Questions

Is Get Highest Answer Rate Question easy or hard?
The problem is rated Medium because it requires familiarity with SQL aggregation and conditional counting. Once you recognize the pattern of computing metrics from event logs, the implementation becomes straightforward.
Get Highest Answer Rate Question Python/Java solution
The standard solution is written in SQL because the problem operates on a relational table. In MySQL, a query with GROUP BY and conditional aggregation calculates answer and show counts and returns the question with the highest answer rate.
How to solve Get Highest Answer Rate Question in O(n)?
Group records by question_id and compute two conditional counts: SUM(action='answer') and SUM(action='show'). Divide these values to get the answer rate for each question. Sorting by the computed rate and selecting the top row returns the correct result in a single table scan.
What is the best approach for Get Highest Answer Rate Question?
The best approach uses SQL conditional aggregation with GROUP BY. Count answers using SUM(action='answer') and shows using SUM(action='show'), compute the ratio, then order by the result in descending order and return the top question_id. This scans the table once and runs in O(n) time.
Is Get Highest Answer Rate Question asked at Google/Amazon/Meta?
This problem reflects real SQL analytics tasks commonly seen in data and backend interviews at companies like Google, Amazon, and Meta. Interviewers often test event-log analysis, aggregation queries, and metric calculations similar to this problem.
What data structure is used in Get Highest Answer Rate Question?
The solution relies on SQL aggregation rather than traditional data structures. Internally, the database engine maintains grouped counters keyed by question_id while executing the GROUP BY operation.
What is the time complexity of Get Highest Answer Rate Question?
The query processes each row once while aggregating by question_id, so the time complexity is O(n) where n is the number of rows in the survey_log table. The database stores intermediate counts per question, leading to O(k) space where k is the number of distinct questions.

Ready to solve this problem?

Practice Get Highest Answer Rate Question with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary