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

# 每种天气类型下，配送速度大于20，投诉率低于50%的每单平均配送时间，总配送次数
with
t1 as(
    select
        staff_id,
        weather_type,
        sum(delivery_time) as total_delivery_time,
        sum(is_complaint) as complaint_times,
        count(delivery_time) as delivery_count

    from
        delivery_records
        left join delivery_staff using(staff_id)
        left join weather_conditions using(weather_id)
    where
        average_speed>20
    group by
        staff_id,
        weather_type
)
,
t2 as(
    select
        weather_type,
        sum(total_delivery_time) as tdt,
        sum(delivery_count) as dc
    from
        t1
    where
        complaint_times/delivery_count<0.5
    group by
        weather_type
)
,
t3 as(
    select
        weather_type,
        round(tdt/dc,2) as average_delivery_time,
        dc as delivery_count
    from
        t2
)

select * from t3