SQL实战练习题(8)-游客旅行取消率-leetcode sql 262

目录

• 问题描述
• 数据SQL
• 参考答案

问题描述

数据SQL

create table users(
	user_id int primary key auto_increment,
	banned Enum('No', 'Yes'),
	`role`  Enum('client','driver', 'partner')
)charset=utf8;

insert into trips (client_id, driver_id, city_id, status, request_at)
values
	(1, 10, 1, 'completed', '2013-10-01'),
	(2, 11, 1, 'cancelled_by_driver', '2013-10-01'),
	(3, 12, 6, 'completed', '2013-10-01'),
	(4, 13, 6, 'cancelled_by_driver', '2013-10-01'),
	(1, 10, 1, 'completed', '2013-10-02'),	
	(2, 11, 6, 'completed', '2013-10-02'),
	(3, 12, 6, 'completed', '2013-10-02'),
	(2, 12, 12, 'completed', '2013-10-03'),
	(3, 10, 12, 'completed', '2013-10-03'),
	(4, 13, 12, 'cancelled_by_driver', '2013-10-03');

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

参考答案

with
tbl_01 as (
	select 
		t1.id,
		t2.user_id,
		t2.`role`,
		t1.request_at,
		t1.status,
		t2.banned
	from trips t1 join users t2 on t1.client_id=t2.user_id
	where t2.banned='No' and t1.request_at between '2013-10-01' and '2013-10-03' and t2.`role` != 'driver'  -- 指定时间内允许用户(不是司机)取消的行
)
,tbl_02 as (
	select request_at, 
		sum(1) as total_count,
		sum(if(status='completed', 1, 0)) as complete_count,
		sum(if(status='cancelled_by_client', 1, 0)) as cancelled_by_client_count,
		sum(if(status='cancelled_by_driver', 1, 0)) as cancelled_by_driver_count
	from tbl_01
	group by request_at
)
select request_at, round((cancelled_by_client_count+cancelled_by_driver_count)/total_count, 2) from tbl_02