Trips and Users
此题有困难，需要学习相关sql语法
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 |
+------------+-------------------+
#1.SELECT * FROM Trips LEFT JOIN Users ON Trips.client_id = Users.users_id ORDER BY request_at 数据预处理
#2.SELECT id,client_id,status,request_at,banned, 筛选数据，获取counter 和request_at
SELECT request_at AS Day, round((max(counter)*1.0/count(request_at)),2) AS "Cancellation Rate"
FROM(
SELECT id,client_id,status,request_at,banned,
@counter := if (@curDate=request_at AND status <> 'completed', @counter+1, 0) ,
@counter := if (@curDate<>request_at AND status <> 'completed', 1, @counter) AS counter,
@curDate := request_at AS curDate
FROM (SELECT * FROM Trips LEFT JOIN Users ON Trips.client_id = Users.users_id ORDER BY request_at) as tmp0,
(SELECT @counter := 0, @curDate := '') as parameters
WHERE banned <> 'Yes' AND request_at >= '2013-10-01' AND request_at <= '2013-10-03'
) AS tmp
GROUP BY request_at
select request_at,round(sum(case when status="completed" then 0 else 1 end)/count(*),2)
from (select * from Trips
where client_id not in
(select users_id from Users where banned = "yes" and role = "client") and request_at >= "2013-10-01" and request_at <= "2013-10-03") t
group by request_at order by request_at asc
