My Easy Solution


  • 0
    J
    SELECT Request_at Day, ROUND(SUM(IF(Status = 'completed', 0, 1))/COUNT(*),2) `Cancellation Rate` 
    FROM Trips t LEFT JOIN Users t1 ON t.Client_Id = t1.Users_Id LEFT JOIN Users t2 ON t.Driver_Id = 
    t2.Users_Id WHERE t1.Banned = 'No' AND t2.Banned = 'No'AND Request_at BETWEEN '2013-10-01' 
    AND '2013-10-03' GROUP BY t.Request_at;
    

    SUM(IF(Status = 'completed', 0, 1)) can get the quantity of cancelled status and count(*) can get all quantity of status. This is the key of this answer;


  • 0
    C

    select Request_at,
    ROUND(
    ( (SUM(Status like 'cancelled_by_%') * 1.0)
    / SUM((Status = 'completed') + (Status like 'cancelled_by_%'))
    )
    , 2) as Rate
    from Trips t, Users u
    where t.Client_Id = u.Users_Id AND Banned = 'No' AND Request_at BETWEEN '2013-10-01' AND '2013-10-03'
    group by Request_at
    ;


Log in to reply
 

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