My accepted solution


  • 0
    H
    select a.request_at as Day,  ROUND(coalesce(cancellation_count,0)/coalesce(total_count,1),2) as "Cancellation Rate" 
    from
    (
        select request_at, count(status) as total_count  from 
        (
            select users_id 
            from users 
            where banned = 'No' and Role='client'
        )A1
        inner join 
        (
                select client_id,
                       request_at,
                       status 
                from Trips 
                where request_at >= cast('2013-10-01' as Date) and request_at <= cast('2013-10-03' as Date)
        )B1 
        on B1.client_id = A1.Users_Id
        group by request_at
    )A
    
    left join
    (
        select request_at, count(status) as cancellation_count  from 
        (
            select users_id 
            from users 
            where banned = 'No' and Role='client'
        )A1
        inner join
        (
            select client_id,
                   request_at,
                   status 
            from Trips 
            where request_at >= cast('2013-10-01' as Date) and request_at <= cast('2013-10-03' as Date)
        )B1 
        on B1.client_id = A1.Users_Id
        and (B1.status = 'cancelled_by_client' or B1.status = 'cancelled_by_driver')
        group by request_at
    )B
    on a. request_at = b.request_at
    where a.request_at >= cast('2013-10-01' as Date) and a.request_at <= cast('2013-10-03' as Date)

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.