leetcode 262. Trips and Users

最新推荐文章于 2024-07-26 07:46:06 发布

原创最新推荐文章于 2024-07-26 07:46:06 发布 · 1.2k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sql server #leetcode #数据库

SQL Server 专栏收录该内容

26 篇文章

订阅专栏

本文介绍了一个SQL查询案例，该查询用于计算特定日期范围内未被封禁客户的出租车行程取消率，并展示了如何使用内连接来筛选符合条件的数据。

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

create table Trips(
	Id int Primary Key,
	Client_Id int,
	Driver_Id int,
	City_Id int,
	Status varchar(200),
	Request_at date
);

insert into Trips values
(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');

create table Users(
	Users_Id int Primary Key,
	Banned char(4),
	Role char(20)
);

insert into Users values
(1,'No','client'),
(2,'Yes','client'),
(3,'No','client'),
(4,'No','client'),
(10,'No','driver'),
(11,'No','driver'),
(12,'No','driver'),
(13,'No','driver');

select t.Request_at,count(case when t.Status in ('cancelled_by_client','cancelled_by_driver') then 1 else null end)*1.00/count(t.Status) Rate
 from Trips t inner join Users u on t.Client_Id=u.Users_Id and u.Banned='NO'
	inner join Users u1 on t.Driver_Id=u1.Users_Id and u1.Banned='NO'
group by t.Request_at