文档章节

262. Trips and Users

52iSilence7
 52iSilence7
发布于 01/04 18:15
字数 617
阅读 11
收藏 0

Description

Difficulty: Hard
Tag: 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 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        |
+------------+-------------------+

Solution

201ms:

select a.Request_at Day, round(sum(case when a.status = 'cancelled_by_driver' then 1 when a.status = 'cancelled_by_client' then 1 else 0 end) /count(1), 2) as 'Cancellation Rate'
from trips a 
where exists (select 1 from users u where u.users_id = a.client_id and u.banned='No')
and exists (select 1 from users u where u.users_id = a.driver_id and u.banned='No')
and a.Request_at between '2013-10-01' and '2013-10-03'
group by a.Request_at

198ms:

select a.Request_at Day, round(sum(case when a.status != 'completed' then 1  else 0 end) /count(1), 2) as 'Cancellation Rate'
from trips a 
where exists (select 1 from users u where u.users_id = a.client_id and u.banned='No')
and exists (select 1 from users u where u.users_id = a.driver_id and u.banned='No')
and a.Request_at between '2013-10-01' and '2013-10-03'
group by a.Request_at

178ms:

select a.Request_at Day, round(sum(case when a.status = 'cancelled_by_driver' then 1 when a.status = 'cancelled_by_client' then 1 else 0 end) /count(1), 2) as 'Cancellation Rate'
from trips a 
left join users d on d.users_id = a.client_id
left join users c on c.users_id = a.client_id
where d.banned='No' and c.banned = 'No'
and a.Request_at between '2013-10-01' and '2013-10-03'
group by a.Request_at

170ms:

select a.Request_at Day, round(sum(case when a.status != 'completed' then 1 else 0 end) /count(1), 2) as 'Cancellation Rate'
from trips a 
left join users d on d.users_id = a.client_id
left join users c on c.users_id = a.client_id
where d.banned='No' and c.banned = 'No'
and a.Request_at between '2013-10-01' and '2013-10-03'
group by a.Request_at

从做这道题了解到了一个知识点,在 left join 中,最外层的 where 条件是施加在 (from, left join) 完成之后的表上的,而不是仅仅影响 left join 中的表。

如果只想要影响要left join 的表,有两种办法

  1. 条件加在 on 后面
  2. 临时表,形式为left join (select * fro users u where u.banned = 'No')

© 著作权归作者所有

52iSilence7

52iSilence7

粉丝 7
博文 109
码字总数 92129
作品 0
海淀
高级程序员
私信 提问
LeetCode 262. Trips and Users

表中存所有出 租 车的行程信息。每段行程有唯一健 Id,ClientId 和 DriverId 是 表中 UsersId 的外键。Status 是枚举类型,枚举成员为 (‘completed’, ‘cancelledbydriver’, ‘cancelled...

猪迪
2018/05/24
99
0
LeetCode:Trips and Users - 出租车接单取消率

1、题目名称 Trips and Users(出租车预约取消率) 2、题目地址 https://leetcode.com/problems/trips-and-users/ 3、题目内容 表Trips内存储了出租车的接单数据,包括Id、ClientId、DriverI...

北风其凉
2015/08/23
1K
0
在另一台电脑上面能够成功运行 为什么会出现这种问题。。。。代码本身没有问题

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sunnyService' defined in file [C:UsersAdministratorworkspace.metadata.pluginsorg.eclipse.......

这人菜的一匹
07/10
254
4
Google Trips更省流量的同时,大数据“窃取”隐私成双刃剑

  Google Maps一直以准确、便利著称,是人们外出旅行不可缺少的帮手。但人们在使用过程中也发现了它的不少缺点和不便之处,如路线规划不简便、离线信息不准确等。现在Google为出国旅行的人...

大数据头条
2018/01/03
0
0
Introducing HTTP Tracing

Introducing HTTP Tracing The Go Programming Language Blog2016-10-0456 阅读 Http Introduction In Go 1.7 we introduced HTTP tracing, a facility to gather fine-grained information ......

The Go Programming Language Blog
2016/10/04
0
0

没有更多内容

加载失败,请刷新页面

加载更多

nginx学习笔记

中间件位于客户机/ 服务器的操作系统之上,管理计算机资源和网络通讯。 是连接两个独立应用程序或独立系统的软件。 web请求通过中间件可以直接调用操作系统,也可以经过中间件把请求分发到多...

码农实战
今天
5
0
Spring Security 实战干货:玩转自定义登录

1. 前言 前面的关于 Spring Security 相关的文章只是一个预热。为了接下来更好的实战,如果你错过了请从 Spring Security 实战系列 开始。安全访问的第一步就是认证(Authentication),认证...

码农小胖哥
今天
11
0
JAVA 实现雪花算法生成唯一订单号工具类

import lombok.SneakyThrows;import lombok.extern.slf4j.Slf4j;import java.util.Calendar;/** * Default distributed primary key generator. * * <p> * Use snowflake......

huangkejie
昨天
12
0
PhotoShop 色调:RGB/CMYK 颜色模式

一·、 RGB : 三原色:红绿蓝 1.通道:通道中的红绿蓝通道分别对应的是红绿蓝三种原色(RGB)的显示范围 1.差值模式能模拟三种原色叠加之后的效果 2.添加-颜色曲线:调整图像RGB颜色----R色增强...

东方墨天
昨天
11
1
将博客搬至CSDN

将博客搬至CSDN

算法与编程之美
昨天
13
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部