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

with t as (
    select order_id,
        d.rider_id,
        TIMESTAMPDIFF(SECOND, pickup_time, deliver_time) / 60.0 duration, 
        case when hour(pickup_time) between 6 and 10 then 'morning'
            when hour(pickup_time) between 11 and 13 then 'noon'
            when hour(pickup_time) between 17 and 20 then 'evening'
            else 'night'
        end time_slot,
        case when date(pickup_time) between date_add(reg_date,interval 7 day) and date_add(reg_date,interval 13 day) then 'W1'
            when date(pickup_time) between date_add(reg_date,interval 14 day) and date_add(reg_date,interval 20 day) then 'W2'
            when date(pickup_time) between date_add(reg_date,interval 28 day) and date_add(reg_date,interval 34 day) then 'W4'
            else null
        end retention_week
    from t_delivery d 
    left join t_rider r on d.rider_id = r.rider_id
    where status='FINISHED' and date(pickup_time) >= date_add(reg_date,interval 7 day)
)

,t1 as(
    select d.rider_id, 
        case when count(distinct order_id)>=30 then 'T1'
            when count(distinct order_id)>=15 and count(distinct order_id)<= 29 then 'T2'
            when count(distinct order_id)>=1 and count(distinct order_id)<= 14 then 'T3'
            else null
        end tier
    from t_delivery d 
    left join t_rider r on d.rider_id = r.rider_id
    where status='FINISHED' and date(pickup_time) <= date_add(reg_date,interval 6 day)
    group by rider_id 
) 

,t2 as (
    select tier,
        count(distinct rider_id) total
    from t1
    where tier is not null
    group by tier
) 

,full as (
    select *
    from (select 'T1' as tier union all select 'T2' union all select 'T3') a
    cross join (select 'W1' as retention_week union all select 'W2' union all select 'W4') b
    cross join (select 'morning' as time_slot union all select 'noon' union all select 'evening' union all select 'night') c
)

select f.tier,
    f.retention_week,
    f.time_slot,
    coalesce(count(distinct t.rider_id),0) active_rider_cnt,
    round(coalesce(count(distinct t.rider_id)/max(total),0),2) retention_rate,
    round(coalesce(avg(duration),0),2) avg_duration_min
from full f
left join t1 on t1.tier = f.tier
left join t on f.retention_week = t.retention_week
    and f.time_slot = t.time_slot
    and t.rider_id = t1.rider_id
left join t2 on t2.tier = f.tier
group by f.tier,
    f.retention_week,
    f.time_slot
order by f.tier,f.retention_week,field(f.time_slot,'morning','noon','evening','night'),active_rider_cnt desc