My AC solution


  • 6
    K
    SELECT question_id as survey_log
    FROM
    (
    	SELECT question_id, SUM(case when action="show" THEN 1 ELSE 0 END) as num_show,    SUM(case when action="answer" THEN 1 ELSE 0 END) as num_answer
    	FROM survey_log
    	GROUP BY question_id
    ) as tbl
    ORDER BY (num_answer / num_show) DESC LIMIT 1
    

  • 0
    A

    @kexiao1612 How to deal, there is two or more same max rate, limit 1 can not have multi question_id


  • 0

    The answer can't pass this test case, weird...

    {"headers": {"survey_log": ["uid", "action", "question_id", "answer_id", "q_num", "timestamp"]},"rows": {"survey_log": [[5, "answer", 285, 123, 1, 1], [5, "answer", 285, 123, 1, 2], [5, "answer", 369, 123, 2, 3], [5, "skip", 369, null, 2, 4]]}}


  • 0
    X

  • 2

    @xukerui

    Finally it works now...

     SELECT question_id AS 'survey_log'
     FROM survey_log
     GROUP BY question_id
     ORDER BY COUNT(answer_id) / COUNT(case when survey_log.action = 
     'show' then survey_log.action else null end) DESC LIMIT 0,1

  • 0
    N
    This post is deleted!

  • 0

    @tongzhou2 said in My AC solution:

    SELECT question_id AS 'survey_log'
    FROM survey_log
    GROUP BY question_id
    ORDER BY COUNT(answer_id) / COUNT(case when survey_log.action =
    'show' then survey_log.action else null end) DESC LIMIT 0,1

    would you mind explaining the solution in this test case?

    {"headers": {"survey_log": ["uid", "action", "question_id", "answer_id", "q_num", "timestamp"]},"rows": {"survey_log": [[5, "answer", 285, 123, 1, 1], [5, "answer", 285, 123, 1, 2], [5, "answer", 369, 123, 2, 3], [5, "skip", 369, null, 2, 4]]}}
    

  • 0
    select question_id as survey_log from survey_log 
    group by question_id 
    order by sum(case when action="answer" then 1 else 0 end) desc limit 1
    

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.