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

# 每个人在7月的总收入（基本工资+派送费总和-支出）

with
t1 as(
    select
        courier_id,
        sum(delivery_fee) as total_df
    from
        deliveries_info
    where
        date_format(delivery_date,'%Y%m')=202407
    group by
        courier_id
)
,
t2 as(
    select
        courier_id,
        sum(expense_amount) as total_ea
    from
        expenses_info
    where
        date_format(expense_date,'%Y%m')=202407
    group by
        courier_id
)
,
t3 as(
    select
        courier_id,
        courier_name,
        base_salary+total_df-total_ea as total_income
    from
        couriers_info
        left join t1 using(courier_id)
        left join t2 using(courier_id)
    order by
        courier_id
)

select * from t3