sql4

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