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

with t1 as 
(select courier_id,sum(delivery_fee) sum_delivery_fee
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) sum_expense_amount
from expenses_info  
where expense_date between '2024-07-01' and '2024-07-31'
group by courier_id)

select couriers_info.courier_id,courier_name,(base_salary+sum_delivery_fee-sum_expense_amount) total_income
from couriers_info join t1 on couriers_info.courier_id=t1.courier_id
join t2 on couriers_info.courier_id=t2.courier_id
order by couriers_info.courier_id