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

with d_fee as (
    select
    courier_id,sum(delivery_fee) as delivery_fee
    from deliveries_info
    where date_format(delivery_date,'%Y-%m')='2024-07'
    group by courier_id
),
    e_amount as (
    select 
    courier_id,sum(expense_amount) as expense_amount
    from expenses_info
    where date_format(expense_date,'%Y-%m')='2024-07' 
    group by courier_id
)
select
c.courier_id,
courier_name,
base_salary+delivery_fee-expense_amount as total_income
from couriers_info c 
inner join d_fee d on c.courier_id=d.courier_id
inner join e_amount e on c.courier_id=e.courier_id;