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

SELECT
    weather_type,
    ROUND(AVG(delivery_time), 2) AS average_delivery_time,
    COUNT(*) AS delivery_count
FROM
    delivery_records
    JOIN weather_conditions USING (weather_id)
WHERE
    staff_id IN (
        SELECT
            staff_id
        FROM
            (
                SELECT
                    weather_type,
                    staff_id
                FROM
                    delivery_records
                    JOIN delivery_staff USING (staff_id)
                    JOIN weather_conditions USING (weather_id)
                GROUP BY
                    weather_type,
                    staff_id
                HAVING
                    AVG(average_speed) > 20
                    AND SUM(is_complaint) / COUNT(record_id) < 0.5
            ) AS t1
    )
GROUP BY
    weather_type
ORDER BY
    weather_type ASC;