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

with 
t1 as(
    select 
        r.record_id,
        r.staff_id,
        w.weather_type,
        s.average_speed,
        r.delivery_time,
        r.is_complaint  
    from delivery_records r 
    left join weather_conditions w on r.weather_id=w.weather_id
    left join delivery_staff s on r.staff_id=s.staff_id),
t2 as(
    select
        staff_id,
        sum(is_complaint)/count(record_id) as complaint_ratio
    from delivery_records
    group by staff_id),
t3 as(
    select 
        t1.staff_id,
        t1.weather_type,
        t1.delivery_time
    from t1 left join t2 on t1.staff_id=t2.staff_id
    where average_speed>20 and t2.complaint_ratio<0.5)

select 
    weather_type,
    round(sum(delivery_time)/count(*),2) as average_delivery_time,
    count(*) as delivery_count
from t3
group by weather_type
order by weather_type