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

with t1 as (
    select courier_id,sum(delivery_fee) as fees
    from deliveries_info
    where left(delivery_date,7)="2024-07"
    group by courier_id
),
t2 as (
    select courier_id,sum(expense_amount) as expenses
    from expenses_info
    where left(expense_date,7)="2024-07"
    group by courier_id
)
select c.courier_id,courier_name,(base_salary+fees-expenses) as total_income
from couriers_info c join t1 on c.courier_id=t1.courier_id
join t2 on c.courier_id=t2.courier_id
order by courier_id