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

-- 查询出每种天气类型下，平均配送速度大于 20 且投诉率（投诉数量 / 配送订单总数量）低于50%
-- 的所有配送员的每单平均配送时间，配送的总次数。查询结果按照天气类型升序排列。

-- 合成大表并选择平均配送速度大于 20的员工
WITH T AS (SELECT D.record_id, D.staff_id, D.delivery_time, D.is_complaint, W.weather_type, S.average_speed
FROM delivery_records D 
LEFT JOIN weather_conditions W
ON D.weather_id = W.weather_id 
LEFT JOIN delivery_staff S
ON D.staff_id = S.staff_id WHERE S.average_speed > 20 ),

T2 AS (SELECT staff_id, weather_type, 
ROUND(AVG(delivery_time),2) AS average_delivery_time,
COUNT(DISTINCT record_id) AS delivery_count,
SUM(is_complaint) / COUNT(DISTINCT record_id) AS complaint_rate
FROM T  GROUP BY staff_id, weather_type
ORDER BY weather_type)

SELECT 
weather_type, ROUND((SUM(average_delivery_time * delivery_count))/SUM(delivery_count),2) AS average_delivery_time , 
SUM(delivery_count) AS delivery_count
FROM T2
WHERE complaint_rate < 0.5
GROUP BY weather_type