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

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

第一步，先找出符合条件的配送员，然后用这个数据对delivery_records数据进行连接筛选，然后正常写。