Trips and Users

发表于   |   分类于 ， ，

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’).

The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).

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.

描述

在 unbanned clients 的前提下，计算出 Oct 1, 2013 and Oct 3, 2013 这段时间内，每一天，Status 不为 completed 的数目所占的比例

代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 方法一

select 
	Trips.Request_at as Day, 
	round(
		sum(case when Trips.Status like 'cancelled%' then 1 else 0 end) / count(*), 2
		) as 'Cancellation Rate' 
from 
	Trips, Users 
where 
	Trips.Client_Id = Users.Users_Id 
	and Users.Banned = 'No' 
	and Trips.Request_at between '2013-10-01' and '2013-10-03' 
group by Trips.Request_at
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 方法二
-- count(false) = 1
-- count(null) = 0
-- select false or null 为 null

select 
	Trips.Request_at as Day, 
	round(
		count(Trips.Status <> 'completed' or null) / count(*), 2
		) as 'Cancellation Rate' 
from 
	Trips, Users 
where 
	Trips.Client_Id = Users.Users_Id 
	and Users.Banned = 'No' 
	and Trips.Request_at between '2013-10-01' and '2013-10-03' 
group by Trips.Request_at
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 方法三

select 
	Trips.Request_at as Day, 
	round(
		count(if(Trips.Status <> 'completed', 1, null)) / count(*), 2
		) as 'Cancellation Rate' 
from 
	Trips 
where 
	Trips.Request_at between '2013-10-01' and '2013-10-03' 
	and Trips.Client_Id not in (select Users_Id from Users where Banned = 'Yes')
group by Trips.Request_at