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

with a as (
    select
        staff_id,
        avg(delivery_time) num,
        sum(is_complaint)/count(*) rate
    from
        delivery_records
    group by 
        staff_id
    having
        num > 20 and rate < 0.5
)
select
    weather_type,
    round(avg(delivery_time),2) average_delivery_time,
    count(*) delivery_count
from
    delivery_records t3
join
    delivery_staff t1 on t1.staff_id = t3.staff_id
join
    weather_conditions t2 on t2.weather_id = t3.weather_id
where
    t3.staff_id in (
        select staff_id from a
    )
group by 
    weather_type
order by 
    weather_type