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

select distinct c.courier_id
,courier_name
,(total_base_salary + total_delivery_fee - total_expense_amount) as total_income
from (
    select courier_id
    ,courier_name
    ,sum(base_salary) over(partition by courier_id) as total_base_salary
    from couriers_info
) c
join (select courier_id
,sum(delivery_fee) over(partition by  courier_id) as total_delivery_fee
from deliveries_info
where substring(delivery_date,1,7) = "2024-07") d on c.courier_id = d.courier_id
join(select courier_id
,sum(expense_amount) over(partition by courier_id) as total_expense_amount
from expenses_info
where substring(expense_date,1,7) = "2024-07") e on c.courier_id = e.courier_id
order by courier_id