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

-- 首先把符合条件的配送员找到：①平均配送速度大于 20 ②投诉率低于50%（这里的投诉率指的是总的投诉率，不区分天气）
-- 找到符合条件的所有配送员后，把他们的订单数据再按天气分组求平均配送时间和总配送次数
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_records join delivery_staff using(staff_id)
    where average_speed > 20
    group by staff_id
    having sum(is_complaint)/count(*) < 0.5
)
group by weather_type
order by weather_type;