leetcode sql 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 users 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        |
+------------+-------------------+

Credits:
Special thanks to @cak1erlizhou for contributing this question, writing the problem description and adding part of the test cases.

---

此题的关键是统计比例，和精度的处理，注意2个点：

1.精度转换CAST (expression AS data_type)函数，AS后面为转换后的类型这里用decimal（9,2）。或者用ROUND(column_name,decimals)函数，第二个参数为精度。

2.count只能统计某一分类下的所有数据个数，但是要统计某一分类下部分数据的个数（这里status！='completed'的数据）该怎么求？？  这里要用sum（）函数配合case when then语句，sum(case t.Status when 'completed' then 0 else 1 end)来求出该group下并满足某些条件的个数。

select t.Request_at Day,cast(sum(case t.Status when 'completed' then 0 else 1 end)/count(*) as decimal(9,2)) 'Cancellation Rate'
from Trips t,Users u1
where t.Client_Id=u1.Users_Id
and u1.Banned!='YES'
and t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at

case ...when..then...when...then...else...end 具体用法见https://www.cnblogs.com/qlqwjy/p/7476533.html

他可以指定一列的值，也可以在分组中使用https://blog.csdn.net/wuzuodingfeng/article/details/53523176