[leetcode] 262. Trips and Users / 취소 비율 구하기

262. Trips and Users

 

https://leetcode.com/problems/trips-and-users/

Trips and Users - LeetCode

 

The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day.

Write a SQL query to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03". Round Cancellation Rate to two decimal points.

 

취소 비율은 '해당 날짜에 차단되지 않은 사용자가 있는 취소된(클라이언트 또는 드라이버에 의한) 요청 수'를

'금지되지 않은 사용자가 있는 총 요청 수'로 나누어 계산합니다.

매일 "2013-10-01"과 "2013-10-03" 사이에 금지되지 않은 사용자(클라이언트와 드라이버 모두 금지되지 않아야 함)가 있는 요청의 취소 비율을 찾기 위해 SQL 쿼리를 작성하십시오. 

라운드 취소 비율은 소수점 둘째 자리입니다.

 

 

정답 코드

SELECT request_at AS Day
    , ROUND((SUM(CASE WHEN status != 'completed' THEN 1 ELSE 0 END) 
         / COUNT(DISTINCT id)),2) AS 'Cancellation Rate'
FROM Trips
WHERE request_at BETWEEN "2013-10-01" AND "2013-10-03"
    AND 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")
GROUP BY request_at;