题解 | 找出补位班次

with min_dt as(
    select
        s.shift_id,
        s.rider_id,
        min(invited_at) as min_date
    from
        relief_shifts r
    left join
        shift_invite_responses s on r.shift_id = s.shift_id
    where
        r.shift_date >= '2025-08-15' and r.shift_date < '2025-08-18' 
        and r.shift_status = 'filled' 
    group by
        s.shift_id,s.rider_id
)
,base as(
    select
        r.store_id,
        r.shift_date,
        r.shift_id,
        r.shift_period,
        r.required_rider_count,
        s.response_id,
        s.rider_id
    from
        relief_shifts r
    left join
        shift_invite_responses s on r.shift_id = s.shift_id
    join
        min_dt m on m.shift_id = s.shift_id and m.rider_id = s.rider_id and m.min_date = s.invited_at
    where
        r.shift_date >= '2025-08-15' and r.shift_date  < '2025-08-18' 
        and r.shift_status = 'filled' and s.response_status = 'accepted' and s.responded_at is not 
        null and date_add(s.invited_at,interval 2 hour) >= s.responded_at
),
agg_ as (
    select
        store_id,
        shift_date,
        shift_id,
        shift_period,
        count(rider_id) as accepted_in_2h_rider_count
    from
        base
    group by
        store_id,shift_date,shift_id,shift_period
),
ran_ as (
    select
        a.store_id,
        a.shift_date,
        a.shift_id,
        a.shift_period,
        a.accepted_in_2h_rider_count,
        r.required_rider_count,
        row_number() over(partition by a.store_id,a.shift_date order by a.accepted_in_2h_rider_count desc ,r.published_at,r.shift_id) as ra_
    from
        agg_ as a
    join
        relief_shifts r on r.shift_id = a.shift_id and r.shift_date=a.shift_date and a.store_id = r.store_id
)

select
    store_id,
    shift_date,
    shift_id,
    shift_period,
    required_rider_count,
    accepted_in_2h_rider_count,
    round(accepted_in_2h_rider_count/required_rider_count,2) as acceptance_rate
from
    ran_
where
    ra_ = 1
order by
    shift_date,store_id,shift_id