题解 | 找出补位班次

WITH t1 AS (
    SELECT shift_id,
           rider_id,
           MIN(invited_at) AS first_invite_time
    FROM shift_invite_responses
    GROUP BY shift_id,rider_id
),
t2 AS (
    SELECT t1.shift_id,
           t1.rider_id,
           s.invited_at,
           s.responded_at,
           s.response_status
    FROM t1 
    LEFT JOIN shift_invite_responses s ON t1.shift_id=s.shift_id AND t1.rider_id=s.rider_id AND t1.first_invite_time=s.invited_at
),
t3 AS (
    SELECT store_id,
           r.shift_id,
           r.shift_date,
           r.shift_period,
           r.published_at,
           MAX(required_rider_count) AS required_rider_count,
           COUNT(DISTINCT CASE WHEN response_status = 'accepted' AND responded_at IS NOT NULL AND responded_at<=DATE_ADD(invited_at,INTERVAL 2 HOUR) THEN rider_id END) AS  accepted_in_2h_rider_count,
            ROUND(COUNT(DISTINCT CASE WHEN response_status = 'accepted' AND responded_at IS NOT NULL AND responded_at<=DATE_ADD(invited_at,INTERVAL 2 HOUR)  THEN rider_id END)/MAX(required_rider_count),2) AS acceptance_rate
    FROM relief_shifts r 
    LEFT JOIN t2  ON r.shift_id=t2.shift_id
    WHERE shift_date BETWEEN '2025-08-15' AND '2025-08-17'
          AND shift_status = 'filled'
    GROUP BY store_id,r.shift_id,r.shift_date,shift_period,published_at
),
t4 AS (
    SELECT store_id,
           shift_date,
           shift_id,
           shift_period,
           required_rider_count,
           accepted_in_2h_rider_count,
           acceptance_rate,
           ROW_NUMBER() OVER(PARTITION BY store_id,shift_date ORDER BY acceptance_rate DESC,accepted_in_2h_rider_count DESC,published_at ASC,shift_id ASC) AS rnk
    FROM t3
)
SELECT store_id,
       shift_date,
       shift_id,
       shift_period,
       required_rider_count,
       accepted_in_2h_rider_count,
       acceptance_rate
FROM t4
WHERE rnk=1
ORDER BY shift_date ASC,store_id ASC,shift_id ASC