Trips and Users

/ mysql / 没有评论 / 389浏览

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        |
+------------+-------------------+

这是LeetCode高难度sql语句题。

题目链接

题目翻译一下:

订单类型有“完成”、“司机取消”、“顾客取消”

用户表,有禁用和启用的用户

要求:查出2013-10-01--2013-10-03每一天,合法顾客的取消率(每一天,合法顾客取消占所有合法用户取消的比率,用2位小数表示)

我的sql语句:

select f.Request_at as Day,ifnull(g.Rate,0.00) as 'Cancellation Rate' from 
(select Request_at from Trips h where h.Request_at BETWEEN '2013-10-01' AND '2013-10-03' group by h.Request_at) f left join
(
SELECT c.Request_at AS DAY,convert(d.numCancell/c.nunAll,decimal(10,2)) AS Rate FROM
(SELECT a.Request_at,COUNT(a.Request_at) AS nunAll 
FROM Trips a ,Users e
WHERE a.Client_Id=e.Users_Id AND e.Banned='No' AND a.Request_at BETWEEN '2013-10-01' AND '2013-10-03' 
GROUP BY a.Request_at) c,
(SELECT b.Request_at,COUNT(b.Request_at) AS numCancell 
FROM Trips b ,Users e
WHERE b.Client_Id=e.Users_Id AND e.Banned='No' AND b.Status <> 'completed' AND b.Request_at BETWEEN '2013-10-01' AND '2013-10-03' 
GROUP BY b.Request_at) d
WHERE c.Request_at = d.Request_at
) g
on( f.Request_at=g.DAY )
点击这里给我发消息
嘿!有什么能帮到您的吗?