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

with
    t1 as (
        select distinct
            courier_id,
            courier_name,
            sum(delivery_fee) over (
                partition by
                    courier_id
            ) as add_fee
        from
            couriers_info
            left join deliveries_info using (courier_id)
        where
            date_format(delivery_date, "%Y-%m") = '2024-07'
    ) ,t2 as (
        select distinct
            courier_id,
            courier_name,
            base_salary - sum(expense_amount) over (
                partition by
                    courier_id
            ) as jian_fee
        from
            couriers_info
            left join expenses_info using (courier_id)
        where
            date_format(expense_date, "%Y-%m") = '2024-07'
    )
select
    t2.courier_id,
    t2.courier_name,
    jian_fee + add_fee as total_income
from
    t2
    left join t1 using (courier_id)