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

# 看准题目
# 条件：配送速度
# 结果：配送时间
select
    weather_type
    ,round(avg(delivery_time), 2) average_delivery_time
    ,count(record_id) delivery_count
from delivery_staff ds
join delivery_records dr on dr.staff_id = ds.staff_id
join weather_conditions wc on wc.weather_id = dr.weather_id
where average_speed > 20
and ds.staff_id in 
    (select
        staff_id
    from delivery_records
    group by 1
    having sum(if(is_complaint = 1, 1, 0)) / count(staff_id) < 0.5)  
group by 1
order by 1;