Inconvenient solution without using sum


  • 0
    H
    select A.Request_at Day, round(B.Status/A.Status,2) "Cancellation Rate" 
    from 
        (select count(Status) Status, Request_at from Trips,Users 
                where Client_Id=Users.Users_Id and Users.Banned='No'  group by Request_at
         ) A,
            
        (select B2.Request_at Request_at, ifnull(Status,0)  Status
                from
                    (select count(Status) Status, Request_at from Trips,Users
                        where Status!='completed'  and Client_Id=Users.Users_Id and Users.Banned='No' group by Request_at) B1
                right join
                    (select distinct Request_at from Trips) B2
                on B2.Request_at = B1.Request_at
        ) B
    where A.Request_at=B.Request_at and date(A.Request_at) between date("2013-10-01") and date("2013-10-03")

  • 0
    S

    I am confused by the question. Why is there a cancellation rate of 0.50 for Oct-03, 2013 which is made by client? The only cancellation for that day is made by driver. So the extect rate for that day should be 0.00. Right?


  • 0
    H

    Both cancellation by client and by driver should be counted.


Log in to reply
 

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