LeetCode_OJ【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 theUsers 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 betweenOct 1, 2013 andOct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded totwo decimal places.

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

要求找出每天unbaned clients的取消记录的概率。

这道题感觉思路还是比较简单的，不知道为什么会评定为hard，下面说下思路：

首先在trip中找出日期区间内符合条件的记录，sql语句如下：

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"

然后对于这些记录根据日期(request_at列)进行分组,找出每组符合status!='completed'条件的元组的个数作为分子，分组内记录个数总数作为分母，分子除以分母得出结果。

本来分子我是这么写的：count(status!='completed') 结果得不到正确的结果，貌似这种写法是错误的；

后来改成了sum( case when status='completed' then 0 else 1 end)的写法，运行通过了；

注意得出来的比率要用round函数取小数点后两位，完整的sql语句如下：

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