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

with
    temp0 as (
        select
            staff_id
        from
            delivery_records dr
        group by
            staff_id
        having
            round(count(if(is_complaint = 1, 1, null)) / count(*),2) < 0.5
    ),
    temp1 as (
        select
            weather_id,
            round(sum(delivery_time) / count(*), 2) as average_delivery_time,
            count(*) as delivery_count
        from
            delivery_records dr
            inner join delivery_staff ds on dr.staff_id = ds.staff_id
            inner join temp0 t0 on t0.staff_id = dr.staff_id
        where
            ds.average_speed > 20
        group by
            weather_id
    )

select
    weather_type,
    average_delivery_time,
    delivery_count
from
    temp1 t1
    inner join weather_conditions wc on t1.weather_id = wc.weather_id
order by
    weather_type;