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

select weather_type,
round(sum(delivery_time*delivery)/sum(delivery),2) as average_delivery_time,
sum(delivery) as delivery_count
from (
select weather_type,
round(avg(delivery_time),2) as delivery_time,
c.staff_id,
sum(is_complaint)/count(*) as ratio,count(*) as delivery
from delivery_records c 
join delivery_staff a 
on c.staff_id=a.staff_id and average_speed >20
join weather_conditions b 
using(weather_id)
group by weather_type,c.staff_id 
) u
where ratio < 0.5
group by weather_type
order by weather_type

烦死了这个题目……