with m1 as (
    #基础数据表基
    select td.order_id,td.rider_id, pickup_time, deliver_time,reg_date,status,
    case when timestampdiff(day,reg_date,pickup_time) between 0 and 6 then 'W0'
    when timestampdiff(day,reg_date,pickup_time) between 7 and 13 then 'W1'
    when timestampdiff(day,reg_date,pickup_time) between 14 and 20 then 'W2'
    when timestampdiff(day,reg_date,pickup_time) between 28 and 34 then 'W4'
    else 'other week'
    end as retention_week, 
    case when time(pickup_time) between '06:00:00' and '10:59:59' then 'morning'
    when time(pickup_time) between '11:00:00' and '13:59:59' then 'noon'
    when time(pickup_time) between '17:00:00' and '20:59:59' then 'evening'
    else 'night'
    end as time_slot,
    timestampdiff(second,pickup_time,deliver_time)/60.0 as duration_min
    from t_delivery td 
    left join t_rider tr on td.rider_id = tr.rider_id
    where status = 'FINISHED'
),
t1 as (
    #查出第一周有单的骑手，并且定级
    select rider_id,
    case when cnt_w0 >= 30 then 'T1'
    when cnt_w0 between 15 and 29 then 'T2'
    when cnt_w0 between 1 and 14 then 'T3'
    end as tier
    from 
    (select rider_id,retention_week,count(1) as cnt_w0 from m1 group by rider_id,retention_week) tt1
    where cnt_w0 !=0 and retention_week = 'W0'
),
t2 as (
    #查出每层w0活跃数
    select tier,count(distinct rider_id) as w0_active_rider_cnt
    from t1
    group by tier
),
base_tb as (
    #建3*3*4的表基
    select *
    from (select 'T1' as tier union select 'T2' UNION SELECT 'T3') tt2
    CROSS join
    (SELECT 'W1' AS retention_week UNION SELECT 'W2' UNION SELECT 'W4') tt3 
    CROSS join
    (SELECT 'morning' AS time_slot UNION SELECT 'noon' UNION SELECT 'evening' UNION SELECT 'night') tt4
),
m2 as (
#查询非空数据
select t1.tier,
m1.retention_week,
m1.time_slot,
count(distinct m1.rider_id) as active_rider_cnt,
round(ifnull(count(distinct m1.rider_id)/avg(t2.w0_active_rider_cnt)*1.0,0),2) as retention_rate, 
round(ifnull(avg(m1.duration_min),0),2) as avg_duration_min
from base_tb
left join m1 
on base_tb.retention_week = m1.retention_week and base_tb.time_slot = m1.time_slot
left join t1 on m1.rider_id = t1.rider_id
left join t2 on t1.tier = t2.tier
where m1.retention_week in ('W1','W2','W4')
group by t1.tier, m1.retention_week, m1.time_slot
)
#连接非空数据表m2与3*3*4的表基并排序得解
select base_tb.tier,base_tb.retention_week,base_tb.time_slot,
ifnull(active_rider_cnt,0) as active_rider_cnt,
ifnull(retention_rate,0) as retention_rate,
ifnull(avg_duration_min,0) as avg_duration_min
from base_tb
left join m2 on base_tb.tier = m2.tier and base_tb.retention_week = m2.retention_week and base_tb.time_slot = m2.time_slot
order by base_tb.tier, base_tb.retention_week,FIELD(base_tb.time_slot, 'morning', 'noon', 'evening', 'night'),active_rider_cnt