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

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

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_