LEETCODE数据库:262.TripsandUsers

发表于 更新于 分类于 Waline： 阅读次数：

题目

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 users (both client and driver must be unbanned) between Oct 1, 2013 and Oct 3, 2013. The cancellation rate is computed by dividing the number of canceled (by client or driver) requests made by unbanned users by the total number of requests made by unbanned users.

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

题解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#正确答案
SELECT t1.Request_at Day,ROUND(SUM(t1.Status!='completed')/COUNT(t1.Id),2) 'Cancellation Rate'
FROM Trips t1 join Users u1 
ON t1.Client_Id=u1.Users_Id AND u1.Banned='No'
join Users u2 ON t1.Driver_Id=u2.Users_Id AND u1.Banned='No'
WHERE t1.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t1.Request_at;

#评论区大量答案
SELECT t1.Request_at Day,ROUND(SUM(t1.Status!='completed')/COUNT(t1.Id),2) 'Cancellation Rate'
FROM Trips t1 join Users u1 
ON t1.Client_Id=u1.Users_Id AND u1.Banned='No'
WHERE t1.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t1.Request_at;

说明

  • 看英文版题目，明确说明了司机和用户都有可能被banned，但是测试数据集没有司机被banned的情况
  • round用来取两位小数