Get Highest Answer Rate Question - Practice Coding Problems

Get Highest Answer Rate Question - Problem

Database Medium

Imagine you're analyzing user behavior on a survey platform! 📊 You have a detailed log of every action users take with survey questions.

Your Mission: Find the question with the highest answer rate - the one that users are most likely to answer when they see it!

The Data: You have a SurveyLog table tracking user interactions:

id: User identifier
action: What the user did - "show" (question displayed), "answer" (user responded), or "skip" (user ignored)
question_id: Which question this was about
answer_id: The answer chosen (null if not answered)
q_num: Question order in the session
timestamp: When this happened

Answer Rate Formula: (Number of "answer" actions) ÷ (Number of "show" actions) for each question

Output: Return the question_id with the highest answer rate. If there's a tie, choose the question with the smallest question_id.

Input & Output

example_1.sql — Basic Survey Data

$ 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: 285

💡 Note: Question 285: 1 answer out of 1 show = 100% answer rate. Question 369: 0 answers out of 1 show = 0% answer rate. Question 285 has the highest answer rate.

example_2.sql — Multiple Questions with Ties

$ Input: SurveyLog table: +----+--------+-------------+-----------+-------+-----------+ | id | action | question_id | answer_id | q_num | timestamp | +----+--------+-------------+-----------+-------+-----------+ | 1 | show | 285 | null | 1 | 100 | | 1 | answer | 285 | 100 | 1 | 101 | | 2 | show | 369 | null | 1 | 102 | | 2 | answer | 369 | 200 | 1 | 103 | | 3 | show | 403 | null | 1 | 104 | | 3 | skip | 403 | null | 1 | 105 | +----+--------+-------------+-----------+-------+-----------+

› Output: 285

💡 Note: Question 285: 1/1 = 100% answer rate. Question 369: 1/1 = 100% answer rate. Question 403: 0/1 = 0% answer rate. Questions 285 and 369 tie at 100%, but 285 has smaller question_id, so it wins.

example_3.sql — Complex Multi-User Scenario

$ Input: SurveyLog table: +----+--------+-------------+-----------+-------+-----------+ | id | action | question_id | answer_id | q_num | timestamp | +----+--------+-------------+-----------+-------+-----------+ | 1 | show | 285 | null | 1 | 100 | | 1 | answer | 285 | 100 | 1 | 101 | | 2 | show | 285 | null | 1 | 102 | | 2 | skip | 285 | null | 1 | 103 | | 3 | show | 369 | null | 1 | 104 | | 3 | answer | 369 | 200 | 1 | 105 | +----+--------+-------------+-----------+-------+-----------+

› Output: 369

💡 Note: Question 285: 1 answer out of 2 shows = 50% answer rate. Question 369: 1 answer out of 1 show = 100% answer rate. Question 369 has the higher answer rate.

Constraints

1 ≤ SurveyLog table rows ≤ 10⁴
Each action is one of: 'show', 'answer', 'skip'
answer_id is null unless action = 'answer'
question_id and answer_id are positive integers
At least one question will have action = 'show'

Visualization

Tap to expand

Understanding the Visualization

Data Collection

Every user interaction (show, answer, skip) is logged with timestamps

Group by Question

SQL GROUP BY creates separate buckets for each question_id

Count Actions

For each question, count 'show' and 'answer' actions using CASE WHEN

Calculate Rates

Answer rate = (answers ÷ shows) × 100% for each question

Find Winner

ORDER BY rate DESC, question_id ASC finds the highest rate with tie-breaking

Key Takeaway

🎯 Key Insight: SQL's GROUP BY with CASE WHEN enables efficient calculation of answer rates for all questions in a single table scan, avoiding the need for multiple queries or complex joins.

Asked in

f Facebook 35 G Google 28 a Amazon 22 ⊞ Microsoft 18

The optimal solution uses SQL GROUP BY aggregation with conditional counting to calculate answer rates for all questions in a single pass. By using CASE WHEN statements within SUM or AVG functions, we can count 'show' and 'answer' actions separately, then sort by answer rate and question_id to find the winner efficiently in O(n) time.

Common Approaches

Approach	Time	Space	Notes
✓ SQL Aggregation with GROUP BY (Optimal)	O(n)	O(k)	Use GROUP BY with conditional counting to calculate all answer rates in one query
Brute Force (Individual Question Analysis)	O(n²)	O(1)	Calculate answer rate for each question separately using subqueries

SQL Aggregation with GROUP BY (Optimal) — Algorithm Steps

Group all records by question_id using GROUP BY
Use SUM(CASE WHEN action = 'show' THEN 1 ELSE 0 END) to count shows
Use SUM(CASE WHEN action = 'answer' THEN 1 ELSE 0 END) to count answers
Calculate answer_rate as answers/shows for each group
Filter out questions with no shows (avoid division by zero)
Order by answer_rate DESC, then question_id ASC for tie-breaking
Use LIMIT 1 to get the top result

Visualization

Tap to expand

Step-by-Step Walkthrough

Group by Question

GROUP BY question_id creates groups for each question

Conditional Count

CASE WHEN counts 'show' and 'answer' actions separately

Calculate Rates

Compute answer_rate = answers/shows for each group

Sort Results

ORDER BY rate DESC, question_id ASC

Get Top Result

LIMIT 1 returns the highest answer rate

Code -

solution.c — C

// Optimal SQL aggregation approach
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[] = 
        "SELECT question_id "
        "FROM ( "
        "    SELECT "
        "        question_id, "
        "        SUM(CASE WHEN action = 'answer' THEN 1 ELSE 0 END) * 1.0 / "
        "        SUM(CASE WHEN action = 'show' THEN 1 ELSE 0 END) AS answer_rate "
        "    FROM SurveyLog "
        "    WHERE action IN ('show', 'answer') "
        "    GROUP BY question_id "
        "    HAVING SUM(CASE WHEN action = 'show' THEN 1 ELSE 0 END) > 0 "
        ") t "
        "ORDER BY answer_rate DESC, question_id ASC "
        "LIMIT 1";
    return query;
}

// Alternative simplified version
char* solutionSimple() {
    static char query[] = 
        "SELECT question_id "
        "FROM SurveyLog "
        "WHERE action IN ('show', 'answer') "
        "GROUP BY question_id "
        "ORDER BY "
        "    AVG(CASE WHEN action = 'answer' THEN 1.0 ELSE 0.0 END) DESC, "
        "    question_id ASC "
        "LIMIT 1";
    return query;
}

int main() {
    printf("%s\n", solution());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the table to group and aggregate, where n is the number of rows

✓ Linear Growth

Space Complexity

O(k)

Space for k unique question_ids in the GROUP BY operation

✓ Linear Space

28.5K Views

Medium Frequency

~15 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

SQL Aggregation with GROUP BY (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler