大厂SQL题3-订单完成率、取消率、排第一

一、订单呼叫

1.1 订单应答率----会做

select sum(wd)/count(1)
from
(
select (case when year(grab_time) = 1970  then 0 else 1 end) wd
from didi_order_rcd
 ) a
1
2
3
4
5
6

1.3 呼叫量最高的是哪一个小时？

分组不是按照年月日小时分组，只是单纯的按照小时一组

select hour(call_time) as t,count(1)
from didi_order_rcd
group by 1
1
2
3

1.4 第二天继续呼叫的比例为？

即次日留存率，同时记得去重

select count(a2.cust_id)/count(a1.cust_id)

from (select distinct cust_id,call_time from didi_order_rcd) a1
left join (select distinct cust_id,call_time from didi_order_rcd) a2

on a1.cust_id=a2.cust_id  and a1.call_time = date_sub(a2.call_time,interval 1 day)
1
2
3
4
5
6

1.5 哪个小时的呼叫应答时间最短？

1，注意筛选出不满足条件的数据
2，时间差函数 timestampdiff(second,小时间,大时间) 结果以秒记

select hour(call_time), 
avg(TIMESTAMPDIFF(second,call_time, grab_time))
from didi_order_rcd
where year(grab_time)<>1970
group by hour(call_time)
1
2
3
4
5

二、货运订单

2.1用车方和司机被禁止(banned=1)的比率分别为？(保留两位小数)–会做

select role,round(sum(banned)/count(1),2)
from hll_t2
group by role
1
2
3

2.2 每天的订单完成率

case when 比较繁琐

select order_dt,sum(bq)/count(1)
from
(
select *,(case when status=completed then 1 else 0) bq
from hll_t1
) a
group by order_dt
1
2
3
4
5
6
7

if(条件，满足输出，不满足输出) 有时候if更加方便
status=‘completed’ 引号

Select order_dt,
sum(if(status='completed',1,0))/count(1) 
from hll_t1
group by order_dt
1
2
3
4

2.3 用车至少两次，且主动取消过至少1次的用车方有多少名？

select usr_id
from 
(select *,(case when status='cancel_by_usr' then 1 else 0 end) bq
from hll_t1) a
group by usr_id
having count(1)>=2  and sum(bq) >=1
1
2
3
4
5
6

select count(a.usr_id) from
(select usr_id, count(1) from hll_t1 group by usr_id having count(1) >=2) a --限制用车至少两次
inner join
(select usr_id, count(1) from hll_t1 
where status= 'cancel_by_usr' --限制主动取消至少 1 次，不要写成 canel，必须是 cancel_by_usr
group by usr_id having count(1)>=1)b 
on a.usr_id =b.usr_id
1
2
3
4
5
6
7

2.4 北京、上海的非禁止用户的用车取消率分别为？（要求输出结果保留两位小数）

sum(if(status=‘cancel_by_usr’,1,0))的用法

select  a.cty,sum(if(status='cancel_by_usr',1,0))/count(1)
from
(select *
from hll_t1 t1 left join hll_t2 t2 on t1.usr_id = t2.usr_id 
where t1.sty in ('北京','上海') and t2.banned = 0) a
group by a.cty
1
2
3
4
5
6

2.5 长沙、北京被用车方取消率排第一的司机编号为？

先求出取消率–排序-- 筛选序列号为1的行

select * from 
(select cty,cancel_rate,dense_rank()over(partition by cancel_rate desc) rnk
from
(select cty,driver_id,sum(if(status='cancel_by_usr',1,0))/count(1) as cancel_rate
from hll_t1
group by cty,driver_id  )  a
)b
where rnk=1
1
2
3
4
5
6
7
8