Get Highest Answer Rate Question - Problem

Given a SurveyLog table that tracks user actions on survey questions, find the question with the highest answer rate.

The table contains:

  • id: User ID
  • action: One of 'show', 'answer', or 'skip'
  • question_id: ID of the question
  • answer_id: ID of the answer (only for 'answer' actions)
  • q_num: Question order in the session
  • timestamp: When the action occurred

The answer rate for a question is calculated as:

Answer Rate = (Number of 'answer' actions) / (Number of 'show' actions)

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

Table Schema

SurveyLog
Column Name Type Description
id int User ID who performed the action
action varchar Action type: 'show', 'answer', or 'skip'
question_id int ID of the question being acted upon
answer_id int ID of the answer (NULL for non-answer actions)
q_num int Sequential order of question in the session
timestamp int Unix timestamp when action occurred
Primary Key: None
Note: Table may contain duplicate rows. Only 'show' and 'answer' actions are used for calculating answer rate.

Input & Output

Example 1 — Basic Answer Rate Calculation
Input Table:
id action question_id answer_id q_num timestamp
5 show 285 1 123
5 answer 285 124 1 124
5 show 369 2 125
5 skip 369 2 126
Output:
question_id
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, so it's returned.
Example 2 — Tie Breaking by Smallest ID
Input Table:
id action question_id answer_id q_num timestamp
1 show 100 1 100
1 answer 100 50 1 101
2 show 200 1 102
2 answer 200 75 1 103
Output:
question_id
100
💡 Note:

Both questions have 100% answer rate (1 answer / 1 show each)

Since there's a tie, we return the question with the smallest question_id

Question 100 < Question 200, so question 100 is returned.
Example 3 — Multiple Shows and Answers
Input Table:
id action question_id answer_id q_num timestamp
1 show 555 1 200
1 show 555 1 201
1 answer 555 10 1 202
2 show 777 1 203
2 skip 777 1 204
Output:
question_id
555
💡 Note:

Question 555: 1 answer out of 2 shows = 50% answer rate

Question 777: 0 answers out of 1 show = 0% answer rate

Question 555 has the higher answer rate and is returned.

Constraints

  • 1 ≤ id ≤ 1000
  • action is one of 'show', 'answer', or 'skip'
  • 1 ≤ question_id ≤ 1000
  • answer_id is NULL for non-answer actions
  • At least one question will have both 'show' and 'answer' actions

Visualization

Tap to expand
Get Highest Answer Rate QuestionInput: Actionsactionquestion_idshow285answer285show369skip369GROUP BY& CALCULATERates per Questionquestion_idrate285100%3690%MAXResultquestion_id285Answer Rate FormulaAnswer Rate =COUNT(answer) / COUNT(show)Q285: 1/1 = 100%
Understanding the Visualization
1
Input
SurveyLog with user actions
2
Group & Count
GROUP BY question_id and count actions
3
Calculate Rate
Compute answer rate and find maximum
Key Takeaway
🎯 Key Insight: Use conditional COUNT aggregation to calculate rates and ORDER BY for proper tie-breaking

Related Problems

Asked in
Facebook 12 Google 8
28.5K Views
Medium Frequency
~12 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