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
Highest Answer Rate Question INPUT: SurveyLog Table uid action q_id answer 5 show 285 null 5 answer 285 124 5 show 369 null 5 skip 369 null 5 show 285 null 5 answer 285 125 Unique Questions: q_id: 285 q_id: 369 Goal: Find question with highest answer rate rate = answers / shows (Tie: pick smallest q_id) ALGORITHM STEPS 1 Group by question_id Collect all actions per q_id 2 Count shows & answers For each question separately q_id shows answers rate 285 2 2 2/2=1.0 369 1 0 0/1=0.0 3 Calculate answer rate rate = answer_cnt / show_cnt 4 Find max rate Order by rate DESC, q_id ASC ORDER BY rate DESC, question_id ASC FINAL RESULT Answer Rate Comparison: q_id 285: 100% 1.0 q_id 369: 0% 0.0 WINNER question_id 285 Output: survey_log | 285 | OK Key Insight: Answer rate = (COUNT of 'answer' actions) / (COUNT of 'show' actions) for each question. Use GROUP BY question_id, then aggregate counts. Handle ties by selecting smallest question_id. SQL: Use conditional SUM (CASE WHEN) or filter with WHERE, then ORDER BY rate DESC, q_id ASC LIMIT 1. TutorialsPoint - Get Highest Answer Rate Question | Optimal Solution

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