The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day.

Write a solution to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03". Round Cancellation Rate to two decimal points.

Return the result table in any order.

The result format is in the following example.

• Solution 1

  SELECT request_at Day
   ,ROUND(SUM(CASE WHEN status = 'cancelled_by_driver' OR status = 'cancelled_by_client' THEN 1 ELSE 0 END) / COUNT(id), 2) 'Cancellation Rate' 
  FROM Trips
  WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
  AND client_id IN (SELECT users_id FROM Users WHERE banned = 'No') -- 서브쿼리
  AND driver_id IN (SELECT users_id FROM Users WHERE banned = 'No') -- 서브쿼리
  GROUP BY request_at;
  

• solution 2

  SELECT request_at as Day, 
       ROUND(COUNT(CASE 
             WHEN status != 'completed' 
             THEN 1 
             ELSE NULL 
             END) / COUNT(*),2) 'Cancellation Rate'
  FROM Trips
  INNER JOIN Users client ON client.users_id = Trips.client_id -- 이너조인1
  INNER JOIN Users driver ON driver.users_id = Trips.driver_id -- 이너조인2
  WHERE (client.banned = 'No' AND driver.banned = 'No') AND (request_at BETWEEN  "2013-10-01" AND "2013-10-03")
  GROUP BY request_at
  

• solution 3

  SELECT Request_at AS Day
      ,ROUND(SUM(IF(Status = 'completed', 0, 1))/COUNT(Status), 2) 'Cancellation Rate' 
  FROM Trips 
  WHERE Client_Id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes') 
    AND Driver_Id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes')
    AND Request_at BETWEEN '2013-10-01' AND '2013-10-03'
  GROUP BY Request_at;