题解 | 找出补位班次

with t1 as (
    select shift_id,rider_id
    from (select *,rank()over(partition by shift_id,rider_id order by invited_at) rak from shift_invite_responses sir ) a
    where response_status = 'accepted'
    and responded_at is not null
    and timestampdiff(second,invited_at,responded_at)/3600<=2
    and rak=1
    group by 1,2)
select store_id,shift_date,shift_id,shift_period,required_rider_count,accepted_in_2h_rider_count,acceptance_rate
from(
select store_id,shift_date,rs.shift_id,shift_period,max(required_rider_count) required_rider_count,count(*) accepted_in_2h_rider_count,round(count(*)/max(required_rider_count),2) acceptance_rate,row_number()over(partition by store_id,shift_date order by round(count(*)/max(required_rider_count),2) desc,count(*) desc,published_at,shift_id) rk
from relief_shifts rs 
join t1 using(shift_id)
where (shift_date between '2025-08-15' and '2025-08-17' )and shift_status = 'filled'
group by 1,2,3,4) t2
where rk=1
order by shift_date,store_id,shift_id