题解 | 了解 2023 年全年所有商品的盈利情况

with t as (
    select product_id, unit_price, sum(quantity) as qty
    from sales_orders
    where year(order_date)=2023
    group by product_id, unit_price
), temp as (
    select t.product_id, 
    (unit_price-purchase_price)*qty as total_profit, 
    round((unit_price-purchase_price)/purchase_price*100, 2) as profit_margin
    from t
    join purchase_prices b on t.product_id=b.product_id
    order by t.product_id
)
select product_id, 
round(sum(total_profit), 2) as total_profit, 
round(avg(profit_margin), 2) as profit_margin
from temp
group by product_id
order by product_id