Accept sample code, need to be impproved


  • 0
    W
    # Write your MySQL query statement below
    select b.Request_at as Day,
        case 
            when cancel is null then round(0,2)
            else round(cancel/total,2)
        end as 'Cancellation Rate'
        from (
            select 
                Request_at,
                count(1) as cancel
            from Trips left join Users
            on Trips.Client_Id = Users.Users_Id 
            where Banned = "No" and Status like "cancel%" and datediff(Request_at,"2013-10-01") in (0,1,2)
            group by Request_at
        ) a
        right join (
            select 
                Request_at,
                count(1) as total
            from Trips left join Users
            on Trips.Client_Id = Users.Users_Id 
            where Banned = "No"  and datediff(Request_at,"2013-10-01") in (0,1,2)
            group by Request_at
        ) b
        on a.Request_at = b.Request_at
        order by Day

  • 0
    A
    SELECT T.Request_at AS Day,  ROUND(SUM(IF(T.Status != 'completed', 1, 0))/ COUNT(*), 2) AS `Cancellation Rate`
    FROM Trips T JOIN Users U ON T.client_id = U.Users_Id JOIN Users DU ON T.driver_id = DU.Users_Id 
    WHERE U.Banned = 'No' AND DU.Banned = 'No' AND T.Request_at BETWEEN '2013-10-01' AND '2013-10-03' 
    GROUP BY T.Request_at
    

Log in to reply
 

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