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

--按照的题目分开去考虑解析:
--每种天气类型下，平均配送速度大于 20 且投诉率（投诉数量 / 配送订单总数量）低于50%的所有配送员的ID
--上面求完符合要求,连接符合人在连接表，求每个天气下的平均速度和平均单数
with tiaojian as (
select 
df.staff_id
from delivery_records ds inner join delivery_staff df 
on ds.staff_id=df.staff_id
inner join weather_conditions ws 
on ws.weather_id=ds.weather_id
group by df.staff_id,ws.weather_id
having avg(delivery_time)>20 and sum(is_complaint=1)/count(record_id)<0.5
)


select 
ws.weather_type,
round(avg(delivery_time),2) as average_delivery_time,
count(ds.record_id) as delivery_count
from  tiaojian t inner join delivery_records ds 
on t.staff_id=ds.staff_id
inner join weather_conditions ws on ws.weather_id=ds.weather_id
group by ws.weather_id
order by ws.weather_type