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

SELECT     
           weather_type,
           
           ROUND(AVG(delivery_time),2) AS average_delivery_time,
           COUNT(record_id) AS delivery_count
FROM delivery_records d 
LEFT JOIN  weather_conditions w ON d.weather_id=w.weather_id
LEFT JOIN  delivery_staff d1   ON d.staff_id=d1.staff_id
WHERE d.staff_id IN (
        SELECT staff_id
        FROM delivery_records
        GROUP BY staff_id
        HAVING COUNT(CASE WHEN is_complaint=1 THEN record_id ELSE NULL END)/COUNT(record_id)<0.5
    ) AND average_speed>20
GROUP BY w.weather_id,weather_type
ORDER BY weather_type