/*
Uber SQL Interview Question

Write a query to find the cancellation rate of requests with unbanned users(both drivers and clients must not be banned ) each day between '2023-10-01' And '2023-10-03'

Cancellation rate is computed by dividing the number of cancelled requests (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day
*/

-- Schema Setup
CREATE TABLE trips (
  id INT NOT NULL,
  client_id VARCHAR(45) NULL,
  driver_id VARCHAR(45) NULL,
  city_id VARCHAR(45) NULL,
  status VARCHAR(45) NULL,
  request_at DATE NULL,
  PRIMARY KEY (id)
);

INSERT INTO trips VALUES 
(1, 1, 10, 1, 'completed', '2023-10-01'),
(2, 2, 11, 1, 'cancelled_by_driver', '2023-10-01'),
(3, 3, 12, 6, 'completed', '2023-10-01'),
(4, 4, 13, 6, 'cancelled_by_client', '2023-10-01'),
(5, 1, 10, 1, 'completed', '2023-10-02'),
(6, 2, 11, 6, 'completed', '2023-10-02'),
(7, 3, 12, 6, 'completed', '2023-10-03'),
(8, 2, 12, 12, 'completed', '2023-10-03'),
(9, 3, 10, 12, 'completed', '2023-10-03'),
(10, 4, 13, 12, 'cancelled_by_driver', '2023-10-03');

CREATE TABLE uber_users (
  user_id INT NOT NULL,
  banned VARCHAR(45) NULL,
  role VARCHAR(45) NULL,
  PRIMARY KEY (user_id)
);

INSERT INTO uber_users VALUES 
(1, 'No', 'client'),
(2, 'Yes', 'client'),
(3, 'No', 'client'),
(4, 'No', 'client'),
(10, 'No', 'driver'),
(11, 'No', 'driver'),
(12, 'No', 'driver'),
(13, 'No', 'driver');

-- Solution
SELECT
   t.request_at,
   ROUND(SUM(CASE WHEN t.status ='cancelled_by_client' OR status = 'cancelled_by_driver' THEN 1 ELSE 0 END)/
   COUNT(t.request_at),2) AS cancellation_rate
FROM trips t 
JOIN uber_users u1
ON u1.user_id = t.client_id
JOIN uber_users u2
ON u2.user_id = t.client_id
WHERE u1.banned = 'No' AND u2.banned = 'No'
GROUP BY t.request_at;