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

大厂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 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.3 呼叫量最高的是哪一个小时?

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

讯享网select hour(call_time) as t,count(1) from didi_order_rcd group by 1 
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.5 哪个小时的呼叫应答时间最短?
讯享网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) 

二、货运订单

在这里插入图片描述

2.1用车方和司机被禁止(banned=1)的比率分别为?(保留两位小数)–会做
select role,round(sum(banned)/count(1),2) from hll_t2 group by role 
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 

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

Select order_dt, sum(if(status='completed',1,0))/count(1) from hll_t1 group by order_dt 
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 
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 
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 
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 
小讯
上一篇 2025-02-06 11:20
下一篇 2025-03-14 21:04

相关推荐

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容,请联系我们,一经查实,本站将立刻删除。
如需转载请保留出处:https://51itzy.com/kjqy/115335.html