Monday, February 11, 2019

SQL question challenge (Cancellation rates for trips)



SQL Schema

The Trips table holds all taxi trips.

TRIPS TABLE.
Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the
+----+----------------+-----------+--------------+--------------------+----------+

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

+----+-----------+-----------+---------+--------------------+----------+

USER TABLE
Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).
The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).


+----------+--------+--------+

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

+----------+--------+--------+
Write a SQL query to find the cancellation rate of requests made by unbanned users
between Oct 1, 2013 and Oct 3, 2013.
For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

DDL SCRIPTS

create table trip (Id int, Client_Id int, Driver_Id int, City_Id int, Status varchar(30),Request_at date);

insert into trip values (1  ,     1     ,    10     ,    1    ,     'completed'      ,'2013-10-01');
insert into trip values (2  ,     2     ,    11     ,    1    , 'cancelled_by_driver','2013-10-01');
insert into trip values (3  ,     3     ,    12     ,    6    ,     'completed'     ,'2013-10-01');
insert into trip values (4  ,     4     ,    13     ,    6    , 'cancelled_by_client','2013-10-01');
insert into trip values ( 5  ,     1     ,    10     ,    1    ,     'completed'      ,'2013-10-02');
insert into trip values ( 6  ,     2     ,    11     ,    6    ,     'completed'     ,'2013-10-02');
insert into trip values ( 7  ,     3     ,    12     ,    6    ,     'completed'      ,'2013-10-02');
insert into trip values ( 8  ,     2     ,    12     ,    12   ,     'completed'      ,'2013-10-03');
insert into trip values ( 9  ,     3     ,    10     ,    12   ,     'completed'      ,'2013-10-03');
insert into trip values ( 10 ,     4     ,    13     ,    12   , 'cancelled_by_driver','2013-10-03');

 
create table users(Users_Id int ,Banned varchar(20), Role varchar(20));

insert into users values (    1     ,   'No'  ,  'client' );
insert into users values (    2     ,   'Yes'  , 'client' );
insert into users values (    3     ,   'No'   , 'client' );
insert into users values (    4     ,   'No'   , 'client' );
insert into users values (    10    ,   'No'   , 'driver' );
insert into users values (    11    ,   'No'   , 'driver' );
insert into users values (    12    ,   'No'   , 'driver' );
insert into users values (    13    ,   'No'   , 'driver' );


SOLUTION 

  1) Get count of trips for unbanned users for all rides
  2) Get count of trips for unbanned users for cancelled rides
  3) cancellation_rate = count of cancelled trip/ count of all rids

select a.request_at as 'Date' ,
      cast(coalesce((b.cancelled_cnt/a.cnt),0.00) as decimal(4,2)) as 'Cancellation Rate'
    from 
M       (select Request_at,count(tr.id) cnt
            from trip tr join users us 
                 on tr.client_id=us.users_id
            where us.banned='no' group by request_at) a
    left outer join
         (select request_at,count(tr.id) cancelled_cnt
        from trip tr join users us
                 on  tr.client_id=us.users_id
            where banned='no' and status !='completed'
          group by request_at) b
    on a.request_at=b.request_at; 
   

No comments:

Post a Comment