262. Trips and Users

最新推荐文章于 2025-04-17 09:31:23 发布

SnailRunFast

最新推荐文章于 2025-04-17 09:31:23 发布

阅读量192

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL

本文链接：https://blog.youkuaiyun.com/snailrunfast/article/details/86622300

SQL 专栏收录该内容

14 篇文章

订阅专栏

本文介绍了一个SQL查询案例，旨在计算2013年10月1日至10月3日期间，由未被封禁的用户发出的请求的取消率。通过对Trips和Users表的联合查询，该查询能精确地计算每天的总请求次数和取消次数，并最终得到取消率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

262. Trips and Users

Hard

158116FavoriteShare

SQL Schema

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 Userstable. 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.

# Write your MySQL query statement below
select 
    total_count.Day                                                     as Day,
    round(ifnull(cancel_count.Total, 0) / total_count.Total, 2)         as 'Cancellation Rate' 
from 
(
    select 
        Trips.Request_at    as Day,
        count(*)            as Total
    from Users, Trips
    where
        Users.Users_id = Trips.Client_Id
        and Users.Banned = 'No'
        and Request_at >= '2013-10-01' and Request_at <= '2013-10-03'
        and Status <> 'completed'
    group by 
        Trips.Request_at
) cancel_count
right outer join
(
    select 
        Trips.Request_at    as Day,
        count(*)            as Total
    from Users, Trips
    where
        Users.Users_id = Trips.Client_Id
        and Users.Banned = 'No'
        and Request_at >= '2013-10-01' and Request_at <= '2013-10-03'
    group by 
        Trips.Request_at
) total_count
on 
    cancel_count.Day = Total_count.Day
;