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

SELECT 
    w1.weather_type,
    ROUND(AVG(dr1.delivery_time),2) AS average_delivery_time,
    COUNT(1) AS delivery_count
FROM
    delivery_records dr1
JOIN 
    weather_conditions w1 ON dr1.weather_id = w1.weather_id
JOIN 
    delivery_staff ds1 ON ds1.staff_id = dr1.staff_id
WHERE dr1.staff_id IN(
    SELECT staff_id 
    FROM (
        SELECT dr2.staff_id,
            AVG(dr2.delivery_time) AS avg_time,
            SUM(dr2.is_complaint)/COUNT(1) AS risk_ratio
        FROM delivery_records dr2
        GROUP BY dr2.staff_id
    )t
    WHERE avg_time > 20 AND risk_ratio < 0.5
)
GROUP BY w1.weather_type
HAVING AVG(dr1.delivery_time) > 20 AND SUM(dr1.is_complaint)/COUNT(1) < 0.5
ORDER BY w1.weather_type ASC