LeetCode—行程和用户

SQL架构：

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'));
Truncate table Trips;
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('1', '1', '10', '1', 'completed', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('3', '3', '12', '6', 'completed', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('5', '1', '10', '1', 'completed', '2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('6', '2', '11', '6', 'completed', '2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('7', '3', '12', '6', 'completed', '2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('8', '2', '12', '12', 'completed', '2013-10-03');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('9', '3', '10', '12', 'completed', '2013-10-03');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03');
Truncate table Users;
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');

查看表记录：
Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

mysql> select * from trips;
+------+-----------+-----------+---------+---------------------+------------+
| 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 |
+------+-----------+-----------+---------+---------------------+------------+
10 rows in set (0.01 sec)

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

mysql> select * from users;
+----------+--------+--------+
| 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 |
+----------+--------+--------+
8 rows in set (0.00 sec)

要求：
写一段 SQL 语句查出 2013年10月1日至 2013年10月3日期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

解法：

mysql> select t.Request_at as day,
    -> round(sum(case when t.status='completed' then 0 else 1 end)/count(*),2) as 'Cancellation Rate'
    -> from
    -> Trips t inner join users u1 on t.Client_Id=u1.Users_Id and u1.Banned='No'
    -> where t.Request_at between '2013-10-01' and '2013-10-03'
    -> group by t.Request_at;
+------------+-------------------+
| day        | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |              0.33 |
| 2013-10-02 |              0.00 |
| 2013-10-03 |              0.50 |
+------------+-------------------+
3 rows in set (0.00 sec)

LeetCode—行程和用户

推荐阅读更多精彩内容