/**
  Problem Name : Trips and Users
  Problem URL : https://leetcode.com/problems/trips-and-users/
  Description :
     Write a SQL query 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".
  Difficulty : Hard
  Language : Oracle SQL
  Category : Database
*/
SELECT  Request_at AS Day, 
        round(sum(CASE WHEN Status IN ('cancelled_by_driver','cancelled_by_client') THEN 1 ELSE 0 END) / count(id), 2) AS "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     to_date(request_at,'YYYY-MM-DD') BETWEEN to_date('2013-10-01','YYYY-MM-DD') AND to_date('2013-10-03','YYYY-MM-DD')
GROUP BY Request_at;