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

with t1 as (
    select *
    from (
        select staff_id, weather_id,
        sum(case when is_complaint=1 then 1 else 0 end)/count(1) as complaint_rate,
        avg(delivery_time) as average_delivery_time
        from delivery_records
        group by staff_id, weather_id
    ) temp
    where complaint_rate<0.5
), t2 as (
    select staff_id
    from delivery_staff
    where average_speed>20
)
select weather_type,
round(avg(delivery_time), 2) as average_delivery_time,
count(1) as delivery_count
from delivery_records t3 join weather_conditions t4 on t3.weather_id=t4.weather_id
where staff_id in (
    select t1.staff_id from t1 join t2 on t1.staff_id=t2.staff_id
)
group by weather_type
order by weather_type