题解 | 饿了么需要分析不同配送员在不同天气条件下的配送效率和用户投诉情况
饿了么需要分析不同配送员在不同天气条件下的配送效率和用户投诉情况
https://www.nowcoder.com/practice/e27ba25e7722478eb86c832fab96fc1a
#每种天气类型下，平均配送速度大于 20 且投诉率（投诉数量 / 配送订单总数量）低于50%的所有配送员的每单平均配送时间，配送的总次数。查询结果按照天气类型升序排列。
with pro_staff as(
select d1.staff_id
from delivery_staff d1 join delivery_records d2 on d1.staff_id=d2.staff_id and average_speed>20
group by d1.staff_id
having sum(is_complaint)/count(*)<0.5
)
select weather_type,round(avg(delivery_time),2) as average_delivery_time,count(*) as delivery_count
from weather_conditions w join delivery_records d on w.weather_id=d.weather_id
where staff_id in(
select *
from pro_staff
)
group by weather_type
order by weather_type
