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

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