题解 | 饿了么需要分析不同配送员在不同天气条件下的配送效率和用户投诉情况

select weather_type
,ROUND(avg(delivery_time),2) as average_delivery_time
,count(is_complaint) as delivery_count
from delivery_records as dr join weather_conditions as wc on dr.weather_id = wc.weather_id
join delivery_staff as ds on dr.staff_id = ds.staff_id
where average_speed > 20 and dr.staff_id in 
(
    SELECT staff_id
    from delivery_records 
    group by staff_id
    having sum(is_complaint) / count(is_complaint) < 0.5
) 
group by weather_type
order by weather_type ASC;