title: Get Highest Answer Rate Question - Leetcode - SQL
body: <p>I'm trying to solve the following Leetcode SQL question:</p>
  <p>Question Link: <a href="https://leetcode.com/problems/get-highest-answer-rate-question/" rel="nofollow noreferrer">https://leetcode.com/problems/get-highest-answer-rate-question/</a></p>
  <p>Description:</p>
  <p>Table: SurveyLog</p>
  <p><a href="https://i.stack.imgur.com/9Bevc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9Bevc.png" alt="enter image description here" /></a></p>
  <p>The answer rate for a question is the number of times a user answered the question by the number of times a user showed the question.</p>
  <p><strong>Write an SQL query to report the question that has the highest answer rate. If multiple questions have the same maximum answer rate, report the question with the smallest question_id.</strong></p>
  <p>The query result format is in the following example.
  <a href="https://i.stack.imgur.com/GgcPQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GgcPQ.png" alt="enter image description here" /></a></p>
  <p>Here's my code:</p>
  <pre><code>WITH Answers AS (
  SELECT question_id, COUNT(question_id) AS ANSWER_COUNT
  FROM SurveyLog
  WHERE action = 'answer'
  GROUP BY question_id),
  
  A AS (SELECT question_id, COUNT(question_id) AS SHOW_COUNT
  FROM SurveyLog
  WHERE action = 'show'|'skip'
  GROUP BY question_id),
  
  B AS (SELECT Answers.question_id, DENSE_RANK() OVER (PARTITION BY Answers.question_id ORDER BY IFNULL((Answers.ANSWER_COUNT/A.SHOW_COUNT), 0) DESC) AS 'Rank' 
  FROM Answers
  INNER JOIN A ON A.question_id = Answers.question_id
  ORDER BY 'Rank' ASC, Answers.question_id ASC)
  
  SELECT DISTINCT question_id AS survey_log FROM B 
  LIMIT 1;
  </code></pre>
  <p>The code passes one testcase below:
  <a href="https://i.stack.imgur.com/9NEwR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9NEwR.png" alt="enter image description here" /></a></p>
  <p>But it fails other testcases:
  <a href="https://i.stack.imgur.com/SPm4m.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SPm4m.png" alt="enter image description here" /></a></p>