139 ms, Straightforward, Using Join Trick

  • 0
    Select X.Day, Round(X.NCancellations * 1.0 / X.NTrips, 2) as 'Cancellation Rate' From
        (Select T1.Request_at as Day, Count(T1.Request_at) as NTrips, Count(T2.Request_at) as NCancellations 
        Trips T1 Left Join Trips T2 
        On T1.Id = T2.Id And T1.Status in ('cancelled_by_client', 'cancelled_by_driver')
        Join Users U1 On T1.Client_Id = U1.Users_Id
        Join Users U2 On T1.Driver_Id = U2.Users_Id
        Where U1.Banned = 'No' And U2.Banned = 'No'
        Group By Day) as X
    Where X.Day Between '2013-10-01' and '2013-10-03'

Log in to reply

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