Simple Logic with Group by


  • 0
    L
    1. We'll calculate cancellation rate for by Day, so we use group by in the outer layer
    2. In the inner layer, construct the dataset that meet the following conditions:
      a. Either client or driver is unbanned
      b. Request date between 10/1/2013 and 10/3/2013
    SELECT Request_at AS Day, 
           ROUND(SUM(CASE WHEN stat != 'completed' THEN 1 ELSE 0 END)/COUNT(Id), 2) AS "Cancellation Rate"
    
    FROM 
           (SELECT Id, Trips.Status as stat, Request_at
            FROM Trips JOIN Users U1 ON Trips.Client_Id = U1.Users_Id JOIN Users U2 ON Trips.Driver_Id = U2.Users_Id
            WHERE U1.Banned = 'No' AND U2.Banned = 'No' AND Request_at between '2013-10-01' and '2013-10-03') as T
    
    GROUP BY Day
    

Log in to reply
 

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