题解 | 饿了么需要分析不同配送员在不同天气条件下的配送效率和用户投诉情况
饿了么需要分析不同配送员在不同天气条件下的配送效率和用户投诉情况
https://www.nowcoder.com/practice/e27ba25e7722478eb86c832fab96fc1a
SELECT
w.weather_type,
ROUND(AVG(dr.delivery_time), 2) AS average_delivery_time,
COUNT(dr.record_id) AS delivery_count
FROM delivery_records dr
INNER JOIN delivery_staff ds ON dr.staff_id = ds.staff_id
INNER JOIN weather_conditions w ON dr.weather_id = w.weather_id
WHERE ds.staff_id IN (
-- 找出平均配送速度>20且投诉率<50%的配送员
SELECT
ds2.staff_id
FROM delivery_staff ds2
LEFT JOIN delivery_records dr2 ON ds2.staff_id = dr2.staff_id
GROUP BY ds2.staff_id, ds2.average_speed
HAVING ds2.average_speed > 20
AND (SUM(CASE WHEN dr2.is_complaint = 1 THEN 1 ELSE 0 END) / COUNT(dr2.record_id)) < 0.5
)
GROUP BY w.weather_type
ORDER BY w.weather_type ASC;
查看25道真题和解析