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

select w.weather_type as weather_type,
round(avg(d.delivery_time),2) as average_delivery_time,
count(*) as delivery_count
from delivery_records d join weather_conditions w on d.weather_id=w.weather_id
where d.staff_id in(
select staff_id from(
    select b.weather_type as weather_type,a.staff_id as staff_id
    from delivery_staff a join delivery_records c on a.staff_id=c.staff_id
    join weather_conditions b on b.weather_id=c.weather_id
    group by b.weather_type,a.staff_id
    having avg(a.average_speed)>20 and sum(case when c.is_complaint=1 then 1 else 0 end)/count(*)<0.5) as t1)
    group by w.weather_type
    order by w.weather_type