[SQL] leetcode Trips and Users

 

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

Trips and Users - LeetCode

 

banned된 고객이 신청한게 아닌 예약들 중에 취소된 비율을 구하는 문제입니다.

오라클 Oracle 풀이

SELECT 
    request_at as Day      
    ,round(COUNT(case status when 'cancelled_by_driver' then 1
                    when 'cancelled_by_client' then 1 else null end)/  count(1), 2) as "Cancellation Rate"
    from Trips T
    where t.request_at between '2013-10-01' and '2013-10-03'
    and exists (select 1
                 from Users U
                where T.client_id = U.users_id
                 and U.banned = 'No')
    and  exists (select 1
                 from Users U
                where T.driver_id = U.users_id
                 and U.banned = 'No')             
    group by request_at