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

with  t1 as (
    select distinct courier_id ,sum(delivery_fee) over (partition by courier_id) as t1
    from deliveries_info
    where delivery_date between '2024-07-01' and '2024-07-31'
),
    t2 as (
        select distinct  courier_id,sum(expense_amount) over(partition by courier_id) as t2
        from expenses_info
        where expense_date between '2024-07-01' and '2024-07-31'
    )
select couriers_info.courier_id,courier_name,round(t1.t1+couriers_info.base_salary-t2.t2,2) as total_income
from t2 join t1 on t1.courier_id=t2.courier_id
join couriers_info on t1.courier_id =couriers_info.courier_id
order by courier_id