Get Highest Answer Rate Question

Get Highest Answer Rate Question

What is this problem about?

The Get Highest Answer Rate Question coding problem is a SQL task. You are given a SurveyLog table containing logs of user actions on survey questions. The actions can be "show", "answer", or "skip". You need to find the question_id that has the highest "answer rate". The answer rate is defined as the number of times a question was answered divided by the number of times it was shown.

Why is this asked in interviews?

Meta (Facebook) uses this Database interview pattern to test a candidate's ability to pivot and aggregate event logs. It evaluates your skills in conditional aggregation (using SUM with CASE WHEN or boolean logic) and calculating ratios. Handling edge cases where questions might be shown but never answered, or handling ties, is critical for real-world telemetry analysis.

Algorithmic pattern used

This problem requires Conditional Aggregation and Sorting.

1. Group By: Group the logs by question_id.
2. Calculate Show Count: Count how many times action = 'show'.
3. Calculate Answer Count: Count how many times action = 'answer'.
4. Calculate Ratio: Divide the answer count by the show count.
5. Sort and Limit: Order the results by the ratio in descending order and limit the output to 1. (If there's a tie, usually order by question_id ascending).

Example explanation

Table:

• Q 285: action 'show'
• Q 285: action 'answer'
• Q 369: action 'show'
• Q 369: action 'show'
• Q 369: action 'skip'

Aggregation:

• Q 285: Answered 1, Shown 1. Rate = $1 / 1 = 1.0$.
• Q 369: Answered 0, Shown 2. Rate = $0 / 2 = 0.0$. Result is 285.

Common mistakes candidates make

• Integer Division: In many SQL dialects, dividing two integers yields an integer (e.g., $1 / 2 = 0$). You must multiply the numerator by 1.0 or CAST to a decimal before dividing.
• Division by Zero: If a question was answered but never shown (bad data), division by zero will cause an error. Using NULLIF(shows, 0) is a safe practice.
• Counting Rows instead of Actions: Using COUNT(*) instead of selectively counting based on the action column.

Interview preparation tip

Master the SUM(IF(condition, 1, 0)) or SUM(CASE WHEN condition THEN 1 ELSE 0 END) syntax. It is the most versatile way to count specific events within a grouped dataset without needing multiple complex subqueries.