Coding Test

Leetcode - 262. Trips and Users

Indo Yoon

문제

Loading...
Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview.
LeetCode

정답

HARD 문제 답게 상당히 복잡했다. 핵심 아이디어는 어떻게 이 ratio를 구하는가? 인 것 같다. CASE WHEN 을 썼을 떄 cancelled_by_client 가 status인 row가 안나와서 혹시나 해서 != 로 필터를 걸었는데 정답이 나왔다. 왜 그런걸까...ㅠㅠ

당연히 문제를 안 읽어서! status는 값이 총 3가지인 ENUM이다.

Status is an ENUM type of ('completed', 'cancelled_by_driver', 'cancelled_by_client').
SELECT request_at AS Day,
ROUND(COUNT(IF(status != 'completed', TRUE, NULL)) /
COUNT(*), 2) AS 'Cancellation Rate'
FROM Trips
WHERE client_id NOT IN (SELECT users_id FROM Users WHERE banned='Yes') AND
driver_id NOT IN (SELECT users_id FROM Users WHERE banned='Yes') AND
request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at