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

select *
from 
(select product_id,sum(total_profit) as total_profit,cast((average_unit_price-purchase_price)/purchase_price*100 as decimal(10,2)) as profit_margin
from
(select order_id,product_id,quantity*(unit_price-purchase_price) as total_profit,cast((unit_price-purchase_price)/purchase_price*100 as decimal(10,2)) as profit_margin ,avg(unit_price) over(partition by product_id) as average_unit_price,purchase_price
from 
    (select
        s.order_id,s.product_id,s.quantity,s.unit_price,s.order_date,p.purchase_price
    from
        sales_orders as s
    inner join 
        purchase_prices as p 
        on s.product_id = p.product_id
    where
        year(order_date)='2023')
    as temp) as temp2
    group by product_id,average_unit_price, purchase_price )
    as temp3