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

#1.先找出符合条件的配送员信息
with t1 as(
    select staff_id
    from (
        select 
            a.staff_id,
            sum(b.is_complaint)/count(1) as sc
        from delivery_staff a 
        join delivery_records  b
        on a.staff_id = b.staff_id 
        where a.average_speed>20
        group by 1
        having sum(b.is_complaint)/count(1)<0.5
    )t
)
#2.按照天气进行分组
# select 
#     w.weather_type,
#     round(avg(d.delivery_time),2) as average_delivery_time,
#     count(1) as delivery_count
#     from weather_conditions w
#     join delivery_records d on d.weather_id = w.weather_id
#     join delivery_staff f on f.staff_id = d.staff_id
# where f.staff_id in (select staff_id from t1)
# group by 1
select 
    w.weather_type,
    round(avg(d.delivery_time),2) as average_delivery_time,
    count(1) as delivery_count
    from weather_conditions w
    join delivery_records d on d.weather_id = w.weather_id
    join t1 f on f.staff_id = d.staff_id
group by 1

1.找出符合条件的配送员信息

2.进行多表关联，按照天气进行分组，此时只需要将第一步得到配送员信息和其他表进行关联即可