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

with 
t1 as(
    select
        courier_id,
        sum(delivery_fee) as sumdeliveryfee
    from deliveries_info
    where delivery_date between '2024-07-01' and '2024-07-31'
    group by courier_id),
t2 as(
    select
        courier_id,
        sum(expense_amount) as sumexpense
    from expenses_info
    where expense_date between '2024-07-01' and '2024-07-31'
    group by courier_id)

select
    c.courier_id,
    c.courier_name,
    c.base_salary+t1.sumdeliveryfee-t2.sumexpense as total_income
from couriers_info c
left join t1 on c.courier_id=t1.courier_id
left join t2 on c.courier_id=t2.courier_id
order by c.courier_id