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

with qual_temp as (
select dr.staff_id,average_speed,sum(is_complaint)/count(record_id) as complain_rate
from delivery_records dr
join delivery_staff ds   on ds.staff_id=dr.staff_id
group by dr.staff_id
)
select weather_type,round(avg(delivery_time),2) as average_delivery_time,
count(*) as delivery_count
from delivery_records dr
join qual_temp q on q.staff_id=dr.staff_id
join weather_conditions  w on w.weather_id=dr.weather_id
where average_speed>20 and  complain_rate<0.5
group by  weather_type
order by  weather_type;