题解 | 25年美团-骑手配送履约分层留存与时段热力矩阵

with base_cnt as
(
    select order_id,d.rider_id,pickup_time,deliver_time,status,
case when hour(pickup_time) between '06:00:00' and "10:59:59" then 'morning'
when hour(pickup_time) between '11:00:00' and "13:59:59" then 'noon'
when hour(pickup_time) between '17:00:00' and "20:59:59" then 'evening'
else 'night' end as time_slot,
case when date(pickup_time) between date(reg_date) and date(date_add(reg_date,interval 6 day)) then 'W0'
when date(pickup_time) between date(date_add(reg_date,interval 7 day)) and date(date_add(reg_date,interval 13 day)) then 'W1'
when date(pickup_time) between date(date_add(reg_date,interval 14 day)) and date(date_add(reg_date,interval 20 day)) then 'W2'
when date(pickup_time) between date(date_add(reg_date,interval 28 day)) and date(date_add(reg_date,interval 34 day)) then 'W4' end as retention_week,
timestampdiff(second,pickup_time,deliver_time)/60 as minute
from t_delivery d left join t_rider r on r.rider_id = d.rider_id where status = "FINISHED"
),
first_cnt as 
(
    select rider_id,
case when w0_cnt >= 30 then "T1"
when w0_cnt between 15 and 29 then 'T2'
when w0_cnt between 1 and 14 then'T3' end as tier
from (
    select rider_id,retention_week,count(*) as w0_cnt from base_cnt group by rider_id,retention_week
)t where retention_week = 'W0' and w0_cnt != 0
),
result as
(
    select f.tier,retention_week,time_slot,
count(distinct c.rider_id) as active_rider_cnt,
round(ifnull(count(distinct c.rider_id),0)/count(distinct f.rider_id)*1.0,2) as retention_rate,
round(avg(minute),2) as avg_duration_min
from base_cnt c join first_cnt f on c.rider_id = f.rider_id
where retention_week in ('W1','W2','W4')
group by tier,retention_week,time_slot
),
base_tb as
(
    select * from
    (select 'T1' as tier union select 'T2' union select 'T3')tt1
    cross join
    (select 'W1' as retention_week union select 'W2' union select 'W4')tt2
    cross join
    (select 'morning' as time_slot union select 'noon' union select 'evening' union select 'night')tt3
)
select b.tier,b.retention_week,b.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 b left join result r on b.tier = r.tier and b.retention_week = r.retention_week and b.time_slot = r.time_slot
order by tier asc,retention_week asc,FIELD(b.time_slot,"morning","noon","evening","night") asc;