Get Highest Answer Rate Question - Solution & Explanation

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.