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

select
    weather_type,
    round(avg(delivery_time), 2) as average_delivery_time,
    count(*) as delivery_count
from 
    (
        select
            staff_id,
            sum(is_complaint) / count(*) as complaint_ratio
        from 
            delivery_records as dr
        where exists(
            select 1
            from delivery_staff as ds
            where ds.staff_id=dr.staff_id and ds.average_speed > 20
        )
        group by staff_id
        having complaint_ratio < 0.5
    ) as tmp 
    inner join delivery_records as dr on dr.staff_id = tmp.staff_id
    inner join weather_conditions as w on dr.weather_id = w.weather_id
group by w.weather_id