-- ① 先算首周单量 → 给骑手打 tier
-- ② 统计每个 tier 的总人数（作为留存率分母）
-- ③ 找 W1/W2/W4 的订单，并打时间段标签
-- ④ 聚合出 active_rider_cnt + avg_duration
-- ⑤ 用 维度表 cross join 补全 36 行
-- ⑥ left join 聚合结果，空值补 0
-- Step1：构造时间窗口（首周 + 各留存周）
with r_date as (
    select
        rider_id,
        reg_date,
        date_add(reg_date,interval 6 day)  as day_6,   -- 首周结束
        date_add(reg_date,interval 7 day)  as day_7,
        date_add(reg_date,interval 13 day) as day_13,
        date_add(reg_date,interval 14 day) as day_14,
        date_add(reg_date,interval 20 day) as day_20,
        date_add(reg_date,interval 28 day) as day_28,
        date_add(reg_date,interval 34 day) as day_34
    from t_rider
),

-- Step2：筛选留存期订单 + 打标签（周 + 时段）
t1 as (
    select
        r.rider_id,
        r.reg_date,
        t.pickup_time,
        t.deliver_time,

        -- 留存周划分
        case 
            when date(t.pickup_time) between r.day_7 and r.day_13 then 'W1' 
            when date(t.pickup_time) between r.day_14 and r.day_20 then 'W2'
            when date(t.pickup_time) between r.day_28 and r.day_34 then 'W4' 
        end as retention_week,

        -- 时段划分（按 pickup_time）
        case 
            when time(t.pickup_time) between '06:00:00' and '10:59:59' then 'morning'
            when time(t.pickup_time) between '11:00:00' and '13:59:59' then 'noon'
            when time(t.pickup_time) between '17:00:00' and '20:59:59' then 'evening'
            else 'night' 
        end as time_slot

    from t_delivery t
    join r_date r on t.rider_id = r.rider_id
    where
        t.status = 'FINISHED'
        -- 只保留留存周订单
        and (
            date(t.pickup_time) between r.day_7 and r.day_13
            or date(t.pickup_time) between r.day_14 and r.day_20
            or date(t.pickup_time) between r.day_28 and r.day_34
        )
),

-- Step3：计算首周单量 → 分层（T1/T2/T3）
t2 as (
    select
        r.rider_id,
        r.reg_date,
        case 
            when sum(case when date(t.pickup_time) between r.reg_date and r.day_6 then 1 else 0 end) >= 30 then 'T1'
            when sum(case when date(t.pickup_time) between r.reg_date and r.day_6 then 1 else 0 end) >= 15 then 'T2'
            when sum(case when date(t.pickup_time) between r.reg_date and r.day_6 then 1 else 0 end) >= 1 then 'T3'
        end as tier
    from t_delivery t
    join r_date r on t.rider_id = r.rider_id
    where t.status = 'FINISHED'
    group by r.rider_id, r.reg_date
    -- 剔除首周 0 单骑手
    having sum(case when date(t.pickup_time) between r.reg_date and r.day_6 then 1 else 0 end) >= 1
),

-- Step4：计算每个 tier 的基准人数（留存率分母）
tier_base as (
    select
        tier,
        count(distinct rider_id) as base_rider_cnt
    from t2
    group by tier
),

-- Step5：聚合留存指标（核心统计）
agg as (
    select
        t2.tier,
        t1.retention_week,
        t1.time_slot,

        -- 在岗骑手数（至少1单）
        count(distinct t1.rider_id) as active_rider_cnt,

        -- 单均时效（分钟）
        round(avg(timestampdiff(second,t1.pickup_time,t1.deliver_time) / 60.0),2) as avg_duration_min

    from t1
    join t2 on t1.rider_id = t2.rider_id
    group by
        t2.tier,
        t1.retention_week,
        t1.time_slot
),

-- Step6：构造维度表（保证输出36行）
t_tier as (
    select 'T1' as tier
    union all select 'T2'
    union all select 'T3'
),

t_retention_week as (
    select 'W1' as retention_week
    union all select 'W2'
    union all select 'W4'
),

t_time_slot as (
    select 'morning' as time_slot, 1 as slot_order
    union all select 'noon', 2
    union all select 'evening', 3
    union all select 'night', 4
)

-- Step7：拼接最终结果（补全缺失组合）
select
    t_t.tier,
    t_r.retention_week,
    t_s.time_slot,

    -- 在岗骑手数（无数据补0）
    ifnull(agg.active_rider_cnt, 0) as active_rider_cnt,

    -- 留存率 = 在岗骑手 / 基准骑手
    round(ifnull(agg.active_rider_cnt, 0) / tier_base.base_rider_cnt, 2) as retention_rate,

    -- 单均时效（无数据补0）
    ifnull(agg.avg_duration_min, 0.00) as avg_duration_min

from t_tier t_t
cross join t_retention_week t_r
cross join t_time_slot t_s
join tier_base on t_t.tier = tier_base.tier
left join agg 
    on t_t.tier = agg.tier
   and t_r.retention_week = agg.retention_week
   and t_s.time_slot = agg.time_slot

-- 排序规则
order by
    t_t.tier,
    t_r.retention_week,
    t_s.slot_order,
    active_rider_cnt desc;