[LeetCode]-DataBase-Trips and Users

The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

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

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

 

需求：查询由未绑定的客户端发起的已经取消的订单的占比

create table Trips(
Id tinyint unsigned auto_increment primary key,
Client_Id tinyint unsigned ,
Driver_Id tinyint unsigned ,
City_Id tinyint unsigned,
Status enum('completed', 'cancelled_by_driver', 'cancelled_by_client'),
Request_at date,
foreign key(Client_Id) references Users(Users_Id),
foreign key(Driver_Id) references Users(Users_Id)
)ENGINE=MyISAM;
create table Users(
Users_Id tinyint unsigned auto_increment primary key,
Banned varchar(10),
Role enum('client', 'driver', 'partner')
)ENGINE=MyISAM;
-- 插入数据
INSERT Trips(Id,Client_Id,Driver_Id,City_Id,Status,Request_at)
VALUES(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')

-- Write a SQL query to find the cancellation rate of requests made by unbanned clients
-- between Oct 1, 2013 and Oct 3, 2013.

-- SQL
SELECT a.dt AS 'Day',IF(b.cnt/a.total IS NULL,0.00,ROUND(b.cnt/a.total,2)) AS 'Cancellation Rate'
FROM (
SELECT t1.Request_at AS dt,COUNT(*) AS total
FROM Trips t1 LEFT JOIN Users t2 ON t2.Users_Id=t1.Client_Id
WHERE t2.Role='client' AND t2.Banned='No' AND t1.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY Request_at
) a LEFT JOIN(
SELECT t1.Request_at AS dt,COUNT(*) AS cnt
FROM Trips t1
LEFT JOIN Users t2 ON t2.Users_Id=t1.Client_Id
WHERE t2.Role='client' AND t2.Banned='No' AND t1.Status LIKE 'cancelled%'
AND t1.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t1.Request_at
) b ON a.dt=b.dt