20211020_trips_and_users.sql

-- Table: Trips

-- +-------------+----------+
-- | Column Name | Type     |
-- +-------------+----------+
-- | Id          | int      |
-- | Client_Id   | int      |
-- | Driver_Id   | int      |
-- | City_Id     | int      |
-- | Status      | enum     |
-- | Request_at  | date     |     
-- +-------------+----------+
-- Id is the primary key for this table.
-- The table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are foreign keys to the Users_Id at the Users table.
-- Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).
 

-- Table: Users

-- +-------------+----------+
-- | Column Name | Type     |
-- +-------------+----------+
-- | Users_Id    | int      |
-- | Banned      | enum     |
-- | Role        | enum     |
-- +-------------+----------+
-- Users_Id is the primary key for this table.
-- The table holds all users. Each user has a unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).
-- Status is an ENUM type of (‘Yes’, ‘No’).
 

-- 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".

-- 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.

-- Return the result table in any order. Round Cancellation Rate to two decimal points.

-- The query result format is in the following example.

 

-- Example 1:

-- Input: 
-- Trips table:
-- +----+-----------+-----------+---------+---------------------+------------+
-- | Id | Client_Id | Driver_Id | City_Id | Status              | Request_at |
-- +----+-----------+-----------+---------+---------------------+------------+
-- | 1  | 1         | 10        | 1       | completed           | 2013-10-01 |
-- | 2  | 2         | 11        | 1       | cancelled_by_driver | 2013-10-01 |
-- | 3  | 3         | 12        | 6       | completed           | 2013-10-01 |
-- | 4  | 4         | 13        | 6       | cancelled_by_client | 2013-10-01 |
-- | 5  | 1         | 10        | 1       | completed           | 2013-10-02 |
-- | 6  | 2         | 11        | 6       | completed           | 2013-10-02 |
-- | 7  | 3         | 12        | 6       | completed           | 2013-10-02 |
-- | 8  | 2         | 12        | 12      | completed           | 2013-10-03 |
-- | 9  | 3         | 10        | 12      | completed           | 2013-10-03 |
-- | 10 | 4         | 13        | 12      | cancelled_by_driver | 2013-10-03 |
-- +----+-----------+-----------+---------+---------------------+------------+
-- Users table:
-- +----------+--------+--------+
-- | Users_Id | Banned | Role   |
-- +----------+--------+--------+
-- | 1        | No     | client |
-- | 2        | Yes    | client |
-- | 3        | No     | client |
-- | 4        | No     | client |
-- | 10       | No     | driver |
-- | 11       | No     | driver |
-- | 12       | No     | driver |
-- | 13       | No     | driver |
-- +----------+--------+--------+
-- Output: 
-- +------------+-------------------+
-- | Day        | Cancellation Rate |
-- +------------+-------------------+
-- | 2013-10-01 | 0.33              |
-- | 2013-10-02 | 0.00              |
-- | 2013-10-03 | 0.50              |
-- +------------+-------------------+
-- Explanation: 
-- On 2013-10-01:
--   - There were 4 requests in total, 2 of which were canceled.
--   - However, the request with Id=2 was made by a banned client (User_Id=2), so it is ignored in the calculation.
--   - Hence there are 3 unbanned requests in total, 1 of which was canceled.
--   - The Cancellation Rate is (1 / 3) = 0.33
-- On 2013-10-02:
--   - There were 3 requests in total, 0 of which were canceled.
--   - The request with Id=6 was made by a banned client, so it is ignored.
--   - Hence there are 2 unbanned requests in total, 0 of which were canceled.
--   - The Cancellation Rate is (0 / 2) = 0.00
-- On 2013-10-03:
--   - There were 3 requests in total, 1 of which was canceled.
--   - The request with Id=8 was made by a banned client, so it is ignored.
--   - Hence there are 2 unbanned request in total, 1 of which were canceled.
--   - The Cancellation Rate is (1 / 2) = 0.50

-- # Write your MySQL query statement below
-- # find the cancellation rate (num cancelled / total num request)
-- # 1. users must not banned
-- # 2. between 2013-10-01 and 03
-- # 3. return the rate to 2 decimal points

-- # get unbanned users of client and driver
-- # get the inner join to exclude any client or driver who are banned

with tmp as (
select tmp1.Status, tmp1.Request_at from

    (select * from Trips
    join 
        (select Users_Id from Users
        where Banned = 'No' and
        Role = 'client') cl
    on cl.Users_Id = Client_Id) tmp1

join

    (select * from Trips
    join 
        (select Users_Id from Users
        where Banned = 'No' and
        Role = 'driver') dr
    on dr.Users_Id = Driver_Id) tmp2

on tmp1.Id = tmp2.Id)

-- # count total # from the date group by
-- # count total cancelled transaction from the date group by
-- # compute the cancellation rate by conditionals where null (from non-existing cancelled transaction table) is replace by 0.00
-- # otherwise compute cancel / total count, rounded to 2 decimal points

select t1.Request_at as Day,
case
    when cc is null then 0.00
    else round(cc/tc, 2)
end as `Cancellation Rate`

from 
        (select *, count(Status) as tc from tmp
        group by Request_at) t1

    left join

        (select *, count(Status) as cc from tmp
        where Status like 'cancelled_by_%'
        group by Request_at) t2

    on t1.Request_at = t2.Request_at

where t1.Request_at between '2013-10-01' and '2013-10-03'



-- shorter version by sachinjha
select t.Request_at Day,
       ROUND((count(IF(t.status!='completed',TRUE,null))/count(*)),2) as 'Cancellation Rate'
from Trips t where 
t.Client_Id in (Select Users_Id from Users where Banned='No') 
and t.Driver_Id in (Select Users_Id from Users where Banned='No')
and t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at;