leetcode-database-262. 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        |

+------------+-------------------+

题意：合法客户发起的订单在2013-10-1到2013-10-3期间内的取消率

思路：

开始理解题意错误，以为是合法客户取消订单的订单率，导致写了sql语句查询结果却和例子不相同，后面理解到是合法客户发起的订单被取消（无论是被客户还是司机取消都算在取消范围内）的订单率。

所以先通过子查询，筛选出日期范围内的，合法客户发起的订单。

(select client_id,driver_id,status,request_at

from Trips t,Users u

where t.client_id=u.users_id

and u.banned='No'

and u.role='client'

and t.request_at<='2013-10-03'

and t.request_at>='2013-10-01'

)

最终sql:

select request_at Day,round(sum(if(status='completed',0,1))/count(1),2) 'Cancellation Rate'

from

(select client_id,driver_id,status,request_at

from Trips t,Users u

where t.client_id=u.users_id

and u.banned='No'

and u.role='client'

and t.request_at<='2013-10-03'

and t.request_at>='2013-10-01'

)T

group by T.request_at;