每日sql--求出租车取消率

求出租车取消率

有两张表，一张Trips表是所有出租车行程的信息，包括行程id、乘客id、司机id、行程状态、日期。
一张Users表是所有用户包括司机、乘客的信息表，包括用户id、是否被禁止、身份。

现求：
‘2019-10-01’ 到 '2019-10-03’这段时间内，非禁止用户（包括乘客、司机）的取消率。
取消率 = （非禁止用户的取消订单量）/非禁止用户的所有订单量

表和数据生成语句：

Create table If Not Exists Trips (Id int, Client_Id int, Driver_Id int, City_Id int, Status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'), Request_at varchar(50));Create table If Not Exists Users (Users_Id int, Banned varchar(50), Role ENUM('client', 'driver', 'partner'));insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (1, 1, 10, 1, 'completed', '2019-10-01');insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (2, 2, 11, 1, 'cancelled_by_driver', '2019-10-01');insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (3, 3, 12, 6, 'completed', '2019-10-01');insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (4, 4, 13, 6, 'cancelled_by_client', '2019-10-01');insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (5, 1, 10, 1, 'completed', '2019-10-02');insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (6, 2, 11, 6, 'completed', '2019-10-02');insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (7, 3, 12, 6, 'completed', '2019-10-02');insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (8, 2, 12, 12, 'completed', '2019-10-03');insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (9, 3, 10, 12, 'completed', '2019-10-03');insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (10, 4, 13, 12, 'cancelled_by_driver', '2019-10-03');insert into Users (Users_Id, Banned, Role) values (1, 'No', 'client');insert into Users (Users_Id, Banned, Role) values (2, 'Yes', 'client');insert into Users (Users_Id, Banned, Role) values (3, 'No', 'client');insert into Users (Users_Id, Banned, Role) values (4, 'No', 'client');insert into Users (Users_Id, Banned, Role) values (10, 'No', 'driver');insert into Users (Users_Id, Banned, Role) values (11, 'No', 'driver');insert into Users (Users_Id, Banned, Role) values (12, 'No', 'driver');insert into Users (Users_Id, Banned, Role) values (13, 'No', 'driver');

解决代码：

select  request_at as day, round(sum(if(t.STATUS = 'completed',0,1 )) / count(t.STATUS),2) as Cancellation
from  Trips as t 
join Users as t1   on t.driver_id = t1.users_id and t1.banned = 'No'
join Users as t2   on t.client_id = t2.users_id and t2.banned = 'No'
where t.request_at BETWEEN '2019-10-01' AND '2019-10-03'
group by request_at