题解 | 找出补位班次

with relief_shifts_sub as (
    select 
    store_id,
    shift_date,
    shift_id,
    shift_period,
    required_rider_count,
    published_at
    from relief_shifts
    where shift_date >= '2025-08-15'
    and shift_date < '2025-08-18'
    and shift_status = 'filled'
),

shift_invite_responses_sub as (
    select 
    shift_id,
    rider_id
    from(
        select
        shift_id,
        rider_id,
        invited_at,
        responded_at,
        response_status,
        row_number()over(partition by shift_id, rider_id order by invited_at) rn
        from shift_invite_responses
    ) sub
    where response_status = 'accepted'
    and responded_at is not null
    and timestampdiff(second, invited_at, responded_at)/3600 <=2
    and rn = 1
),


base as (
    select
    r.store_id,
    r.shift_date,
    r.shift_id,
    r.shift_period,
    r.required_rider_count,
    count(s.rider_id) accepted_in_2h_rider_count,
    round(count(s.rider_id) / r.required_rider_count, 2) acceptance_rate,
    r.published_at
    from relief_shifts_sub r
    left join shift_invite_responses_sub s on r.shift_id = s.shift_id
    group by r.store_id, r.shift_date, r.shift_id, r.shift_period, r.required_rider_count
),

base_rn as (
    select
    shift_id,
    row_number()over(partition by store_id, shift_date order by acceptance_rate desc, 
    accepted_in_2h_rider_count desc, published_at, shift_id) rn
    from base
)

select 
store_id,
shift_date,
shift_id,
shift_period,
required_rider_count,
accepted_in_2h_rider_count,
acceptance_rate
from base join base_rn using (shift_id)
where rn = 1
order by shift_date, store_id, shift_id