Castle，专属于自己的领地

我的沉思、笔记和回忆

0%

LeetCode: 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’).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
+----+-----------+-----------+---------+--------------------+----------+
| 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’).

1
2
3
4
5
6
7
8
9
10
11
12
+----------+--------+--------+
| 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.

1
2
3
4
5
6
7
+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

翻译

Trips 表中包含了所有的出租车行程。每个行程都有一个唯一的 Id，Client_Id 字段和 Driver_Id 字段都是 Users 表的外键。Status 字段的类型是枚举，包含 ‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’ 四种情况。 Users 表包含了所有用户。每个用户都有唯一的 Users_Id， Role 字段的类型是枚举， 包含 ‘client’, ‘driver’, ‘partner’ 三种情况。 写出能够找出10月1号到3号间未冻结账户订单取消率的 sql 语句。取消率保留两位小数。

解决方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
SELECT
    A.Request_at AS 'Day',
    Round(
        IFNULL(B.canceled, 0) / A.total,
        2
    ) AS 'Cancellation Rate'
FROM
    (
        SELECT
            count(Request_at) AS total,
            Request_at
        FROM
            Trips,
            Users
        WHERE
            Trips.client_id = Users.Users_id
        AND Users.Banned != "Yes"
        AND Trips.Request_at >= "2013-10-01"
        AND Trips.Request_at <= "2013-10-03"
        GROUP BY
            Request_at
    ) A
LEFT JOIN (
    SELECT
        count(Request_at) AS canceled,
        Request_at
    FROM
        Trips,
        Users
    WHERE
        Trips.Client_id = Users.Users_id
    AND Users.Banned != "Yes"
    AND Trips.Request_at >= "2013-10-01"
    AND Trips.Request_at <= "2013-10-03"
    AND (
        Trips.`Status` = 'cancelled_by_client'
        OR Trips.`Status` = 'cancelled_by_driver'
    )
    GROUP BY
        Request_at
) B ON A.Request_at = B.Request_at