LeetCode 578. Get Highest Answer Rate Question SQL Solution

Problem

LeetCode SQL Problem

578. Get Highest Answer Rate Question

survey_log table

id	action	question_id	answer_id	q_num	timestamp
5	show	285		1	123
5	answer	285	124124	1	124
5	show	369		2	125
5	skip	369		2	126

Solution

MySQL

Get Highest Answer Rate Question

WITH answer_rate_analysis
AS (
	SELECT question_id
		,COUNT(answer_id) / CAST(SUM(CASE
					WHEN action = 'show'
						THEN 1
					ELSE 0
					END) AS FLOAT) AS answer_rate
	FROM survey_log
	GROUP BY question_id
	)
SELECT TOP 1 question_id AS survey_log
FROM answer_rate_analysis
ORDER BY answer_rate DESC

TSQL

Get Highest Answer Rate Question

WITH answer_rate_analysis
AS (
	SELECT question_id
		,COUNT(answer_id) / CAST(SUM(CASE
					WHEN action = 'show'
						THEN 1
					ELSE 0
					END) AS FLOAT) AS answer_rate
	FROM survey_log
	GROUP BY question_id
	)
SELECT TOP 1 question_id AS survey_log
FROM answer_rate_analysis
ORDER BY answer_rate DESC

Query Output

survey_log
285