题解 | 物流公司想要分析快递小哥的薪资构成和绩效情况

WITH
t1 AS (
SELECT
    courier_id,
    SUM(delivery_fee) AS tc
FROM deliveries_info
WHERE delivery_date >= '2024-07-01' AND delivery_date < '2024-08-01'
GROUP BY courier_id
),

t2 AS (
SELECT
    courier_id,
    SUM(expense_amount) AS zc
FROM expenses_info
WHERE expense_date >= '2024-07-01' AND expense_date < '2024-08-01'
GROUP BY courier_id
)

SELECT
    a.courier_id,
    a.courier_name,
    COALESCE(a.base_salary,0) + COALESCE(t1.tc,0) - COALESCE(t2.zc,0) AS total_income
FROM couriers_info AS a
LEFT JOIN t1 ON a.courier_id = t1.courier_id
LEFT JOIN t2 ON a.courier_id = t2.courier_id
ORDER BY a.courier_id;