My solution


  • 0
    S
    # Write your MySQL query statement below
    SELECT Trips_Made_By_Unbanned_Users.Day, ROUND (
          ( CASE 
            WHEN Canceled_Trips_With_Unbanned_Users.count2 IS NULL THEN 0 
            ELSE Canceled_Trips_With_Unbanned_Users.count2 END)
      / Trips_Made_By_Unbanned_Users.count1, 2 ) AS "Cancellation Rate"
    FROM
    ( SELECT t1.Request_at AS Day, COUNT(t1.Status) AS count1
    FROM Trips t1, Users u1
    WHERE t1.Request_at BETWEEN '2013-10-01'AND '2013-10-03' AND 
      u1.Banned = "No" AND t1.Client_Id = u1.Users_Id
    GROUP BY t1.Request_at ) Trips_Made_By_Unbanned_Users
    LEFT JOIN
    ( SELECT t2.Request_at AS Day, COUNT(t2.Status) AS count2
    FROM Trips t2, Users u2
    WHERE t2.Request_at BETWEEN '2013-10-01'AND '2013-10-03' AND 
      u2.Banned = "No" AND
      t2.Status != "completed" AND
      t2.Client_Id = u2.Users_Id
    GROUP BY t2.Request_at ) Canceled_Trips_With_Unbanned_Users
    ON Trips_Made_By_Unbanned_Users.Day = Canceled_Trips_With_Unbanned_Users.Day
    
    

Log in to reply
 

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