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

select weather_type
,round(avg(delivery_time),2) as average_delivery_time
,count(*) as delivery_count
from delivery_records
join weather_conditions using(weather_id)
where staff_id in (
    select staff_id
    from delivery_staff
    where average_speed > 20
)
and staff_id in(
    select staff_id
    from delivery_records
    group by staff_id
    having sum(is_complaint) / count(is_complaint) < 0.5
)
group by weather_type
order by weather_type