Leetcode 262. 行程和用户

1.题目基本信息

1.1.题目描述

表：Trips

+-------------+----------+

| Column Name | Type |

+-------------+----------+

| id | int |

| client_id | int |

| driver_id | int |

| city_id | int |

| status | enum |

| request_at | varchar |

+-------------+----------+

id 是这张表的主键（具有唯一值的列）。

这张表中存所有出租车的行程信息。每段行程有唯一 id ，其中 client_id 和 driver_id 是 Users 表中 users_id 的外键。

status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。

表：Users

+-------------+----------+

| Column Name | Type |

+-------------+----------+

| users_id | int |

| banned | enum |

| role | enum |

+-------------+----------+

users_id 是这张表的主键（具有唯一值的列）。

这张表中存所有用户，每个用户都有一个唯一的 users_id ，role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。

banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。

取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。

编写解决方案找出 "2013-10-01" 至 "2013-10-03" 期间有 至少 一次完整行程的非禁止用户（乘客和司机都必须未被禁止）的 取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。

返回结果表中的数据 无顺序要求 。

1.2.题目地址

https://leetcode.cn/problems/trips-and-users/description/

2.解题方法

2.1.解题思路

按日期进行分组聚合

2.2.解题步骤

第一步，根据user_id、client_id、driver_id，将Users表格连接到Trips表格中，得到表格T1

第二步，T1的基础上使用request_at日期进行分组聚合，统计每组中SUM(client_banned="No" AND driver_banned='No' AND status!="completed")/SUM(client_banned="No" AND driver_banned='No')的比例，记为rate

第三步，从T2中筛选出rate非NULL的记录，即为题解

3.解题代码

sql代码

# Write your MySQL query statement below

# 思路：按日期进行分组聚合

WITH T1 AS (
    # 第一步，根据user_id、client_id、driver_id，将Users表格连接到Trips表格中，得到表格T1
    SELECT 
        client_id, driver_id, status, request_at, 
        u1.banned AS client_banned, 
        u2.banned AS driver_banned
    FROM Trips
        LEFT JOIN Users AS u1 ON Trips.client_id = u1.users_id
        LEFT JOIN Users AS u2 ON Trips.driver_id = u2.users_id
), T2 AS (
    # 第二步，T1的基础上使用request_at日期进行分组聚合，统计每组中SUM(client_banned="No" AND driver_banned='No' AND status!="completed")/SUM(client_banned="No" AND driver_banned='No')的比例，记为rate
    SELECT 
        request_at AS Day, 
        ROUND(SUM(client_banned = "No" AND driver_banned = 'No' AND status != "completed") / SUM(client_banned = "No" AND driver_banned = 'No'), 2) AS rate
    FROM T1
    WHERE request_at >= '2013-10-01' AND request_at <= '2013-10-03'
    GROUP BY request_at
)

# 第三步，从T2中筛选出rate非NULL的记录，即为题解
SELECT 
    Day, 
    rate AS 'Cancellation Rate'
FROM T2 WHERE rate IS NOT NULL;

4.执行结果