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

# 1、筛选合格的配送员
with t1 as (
    select
    dr.staff_id
    ,sum(is_complaint)/count(distinct record_id) as c_rate
    from delivery_records dr
    left join delivery_staff ds
    on dr.staff_id = ds.staff_id
    where average_speed > 20
    group by dr.staff_id
)

select
weather_type 
,round(avg(delivery_time ), 2) as average_delivery_time
,count(*) as delivery_count
from delivery_records ds
left join weather_conditions w
on ds.weather_id = w.weather_id 
where ds.staff_id in (
    select staff_id from t1 where c_rate < 0.5
)
group by ds.weather_id
order by weather_type
;