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

#先找出符合条件的配送员
WITH satff_filter AS (
        SELECT
            staff_id
        FROM
            (
                SELECT
                    ds.staff_id,
                    SUM(dr.is_complaint) / COUNT(*) AS cr
                FROM
                    delivery_staff AS ds
                    INNER JOIN 
                    delivery_records AS dr ON ds.staff_id = dr.staff_id
                WHERE
                    average_speed > 20
                GROUP BY
                    staff_id
                HAVING
                    cr < 0.5
            ) AS satff
    )
#将符合条件的配送员和其他表连接
SELECT
    w.weather_type,
    ROUND(AVG(dr.delivery_time),2) AS average_delivery_time,
    COUNT(*) AS delivery_count
FROM
    satff_filter AS sf
    INNER JOIN
    delivery_records AS dr ON sf.staff_id=dr.staff_id
    INNER JOIN
    weather_conditions AS w ON dr.weather_id=w.weather_id
GROUP BY
    w.weather_type;