SQLPad Forum question: #85 why is my query incorrect?

#85 why is my query incorrect?

Hi,
i compared my query with the solution, however I don't see any significant difference in grammar. Would appreciate it if you can spot the bugs for me.
SELECT query
FROM search s
JOIN search_result r
ON s.search_id = r.search_id
WHERE s.date = '2021-01-01'
GROUP BY query
HAVING COUNT(DISTINCT s.user_id) > 2
ORDER BY COUNT(DISTINCT CASE WHEN action = 'click' THEN s.search_id ELSE NULL END)/COUNT(DISTINCT s.search_id) DESC
LIMIT 5

Answers

Comment

Hey Cyn,
The small bug happened during the computing of click through rate, a common trick is to multiply the numerator with 1.0 to convert them into floats, otherwise you get 0 (by default).
SELECT query
FROM search s
JOIN search_result r
ON s.search_id = r.search_id
WHERE s.date = '2021-01-01'
GROUP BY query
HAVING COUNT(DISTINCT s.user_id) > 2
ORDER BY COUNT(DISTINCT CASE WHEN action = 'click' THEN s.search_id ELSE NULL END) * 100.0 /COUNT(DISTINCT s.search_id) DESC
LIMIT 5
--Leon

Note that the * 1.0 (or * 100.0 depending on context for percentages) comes up often in interviews as a test of your sql experience.

Mike, Feb. 15, 2022, 5:37 a.m.

Leon (949)

Feb. 12, 2022, 9:15 a.m.