【携程】统计门店每日补位班次 2 小时内确认率 Top1

题目描述

来源：携程 SQL 面试真题

某即时零售平台在晚高峰前，会给门店的骑手补位班次发送“待确认邀约”。同一个班次可能会在不同时间多次推送给不同骑手，也可能被同一骑手多次看到。运营团队希望统计指定日期范围内、每个门店每天最终成功开班的补位班次在发出邀约后的 2 小时内确认情况，并找出门店当日表现最好的补位班次。

数据表

relief_shifts 补位班次主表

shift_invite_responses 班次邀约反馈表

业务规则

1. 只统计 shift_date 在 2025-08-15 到 2025-08-17 之间且 shift_status = 'filled' 的补位班次。
2. 对同一个 shift_id + rider_id，只保留 invited_at 最早的那条记录作为该骑手的首轮邀约记录；如果时间相同，使用 response_id 更小的记录保证结果稳定。
3. 2 小时内确认成功必须同时满足：response_status = 'accepted'、responded_at 不为 NULL、responded_at 不晚于 invited_at 后 2 小时。
4. accepted_in_2h_rider_count 是班次内满足条件的不同骑手数。
5. acceptance_rate = accepted_in_2h_rider_count / required_rider_count，四舍五入保留 2 位小数。
6. 每个 store_id + shift_date 只保留 acceptance_rate 最高的一个班次。
7. 如果确认率相同，依次比较 accepted_in_2h_rider_count 更大、published_at 更早、shift_id 更小。

输出字段

结果按 shift_date 升序、store_id 升序、shift_id 升序排列。