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

select 
    weather_type,
    round(avg(delivery_time), 2) as average_delivery_time,
    count(record_id) as delivery_count
from delivery_records a 
left join weather_conditions b 
on a.weather_id = b.weather_id 
left join delivery_staff c 
on a.staff_id = c.staff_id
where (weather_type, a.staff_id) in (
    select 
        weather_type,
        a.staff_id
    from delivery_records a 
    left join weather_conditions b 
    on a.weather_id = b.weather_id 
    left join delivery_staff c 
    on a.staff_id = c.staff_id
    where average_speed > 20 
    group by 1,2
    having sum(is_complaint)/count(record_id) < 0.5
)
group by 1
order by 1