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

with t1 as (select staff_id,average_speed from delivery_staff
where average_speed > 20),
t2 as (
select t1.staff_id as staff_id,weather_id,delivery_time,is_complaint from delivery_records d 
join t1
on t1.staff_id = d.staff_id
),
t3 as (
select staff_id,weather_id from t2
group by staff_id,weather_id
having sum(is_complaint)/count(1) < 0.5
),
t4 as (select t3.staff_id,t3.weather_id as weather_id,delivery_time,is_complaint from t2
join t3
on t3.staff_id = t2.staff_id and t3.weather_id = t2.weather_id),
t5 as (select t4.weather_id,weather_type,delivery_time,is_complaint from t4
join weather_conditions w
on t4.weather_id = w.weather_id)
select weather_type,round(avg(delivery_time),2) as average_delivery_time,count(1) as delivery_count from t5
group by weather_type
order by weather_type