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

select
    a.courier_id,
    courier_name,
    round(
        avg(base_salary) + sum(delivery_fee) / count(distinct c.expense_id) - sum(expense_amount) / count(distinct b.delivery_id),
        2
    ) total_income
from
    couriers_info a
    left join deliveries_info b on a.courier_id = b.courier_id
    left join expenses_info c on a.courier_id = c.courier_id
where
    date_format(delivery_date, '%Y-%m') = '2024-07'
    and date_format(expense_date, '%Y-%m') = '2024-07'
group by
    1,
    2
order by
    1