请问这题用 SQL server 怎么解？

Question

假设以下为嘀嘀打车的行程表 Trips，Id 是唯一键，Client_Id、Driver_Id 分别是乘客和司机的编号，Status 的三种状态分别意思为 完成、乘客取消、司机取消
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1 | 1 | 10 | 1 | completed |2018-10-01|
| 2 | 2 | 11 | 1 | cancelled_by_driver |2018-10-01|
| 3 | 3 | 12 | 6 | completed |2018-10-01|
| 4 | 4 | 13 | 6 | cancelled_by_client |2018-10-01|
| 5 | 1 | 10 | 1 | completed |2018-10-02|
| 6 | 2 | 11 | 6 | completed |2018-10-02|
| 7 | 3 | 12 | 6 | completed |2018-10-02|
| 8 | 2 | 12 | 12 | completed |2018-10-03|
| 9 | 3 | 10 | 12 | completed |2018-10-03|
|10 | 4 | 13 | 12 | cancelled_by_driver |2018-10-03|
+----+-----------+-----------+---------+--------------------+----------+

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示 乘客、司机 的类型。
+----------+--------+--------+
| 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 |
+----------+--------+--------+

写一段 SQL 语句查出 2018年10月1日 至 2018年10月3日 期间非禁止乘客的取消率。基于上表，你的 SQL 语句应返回如下结果，结果保留两位小数。
+------------+-------------------+
| Day | CancellationRate |
+------------+-------------------+
| 2018-10-01 | 0.33 |
| 2018-10-02 | 0.00 |
| 2018-10-03 | 0.50 |
+------------+-------------------+

Answer 1

Select (a.d/b.d) 取精度自己查一下
(Select d=Request_at,all=count(1) from Trips t where datadiff(d,Request_at,'2018-10-01')<=0 and datadiff(d,Request_at,'2018-10-03')>=0 goup by Request_at) as a
inner join
(Select d=t.Request_at,all=count(1) from Trips t
inner join Users u on u.Banned = No and u.Users_Id = t.Client_Id
where datadiff(d,t.Request_at,'2018-10-01')<=0 and datadiff(d,t.Request_at,'2018-10-03')>=0 goup by Request_at) as b
on a.d=b.d