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

with t1 as (
    select courier_id, sum(delivery_fee) as sum_delivery_fee
    from deliveries_info
    where date_format(delivery_date,'%Y-%m') = '2024-07'
    group by courier_id
),
t2 as (
    select courier_id, sum(expense_amount) as sum_expense_amount
    from expenses_info
    where date_format(expense_date,'%Y-%m') = '2024-07'
    group by courier_id
)
select courier_id, courier_name,
base_salary + sum_delivery_fee - sum_expense_amount as total_income
from couriers_info
inner join t1 using(courier_id)
inner join t2 using(courier_id)
order by courier_id asc;