题解 | 找出补位班次

with
    lsb as (
        select
            r.store_id,
            r.shift_date,
            r.shift_id,
            r.shift_period,
            r.required_rider_count,
            count(l.rider_id) as accepted_in_2h_rider_count,
            round(count(l.rider_id) / r.required_rider_count, 2) as acceptance_rate,
            row_number() over (
                partition by
                    r.store_id,
                    r.shift_date
                order by
                    round(count(l.rider_id) / r.required_rider_count, 2) desc,
                    count(l.rider_id) desc,
                    r.published_at asc,
                    r.shift_id asc
            ) as rk
        from
            relief_shifts r
            left join (
                select
                    response_id,
                    shift_id,
                    rider_id,
                    invited_at,
                    responded_at,
                    response_status
                from
                    (
                        select
                            *,
                            row_number() over (
                                partition by
                                    shift_id,
                                    rider_id
                                order by
                                    invited_at
                            ) as rk
                        from
                            shift_invite_responses
                    ) t
                where
                    t.rk = 1
            ) l on r.shift_id = l.shift_id
        where
            r.shift_date between '2025-08-15' and '2025-08-17'
            and r.shift_status = 'filled'
            and l.response_status = 'accepted'
            and l.responded_at is not null
            and l.responded_at <= timestampadd(hour, 2, invited_at)
        group by
            r.store_id,
            r.shift_date,
            r.shift_id,
            r.shift_period,
            r.required_rider_count
    )
select
    store_id,
    shift_date,
    shift_id,
    shift_period,
    required_rider_count,
    accepted_in_2h_rider_count,
    acceptance_rate
from
    lsb
where
    lsb.rk = 1
order by
    shift_date,
    store_id,
    shift_id