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

select 
product_id,
sum(total_profit) as total_profit,
round(avg(profit_margin),2) as profit_margin
from (
select 
product_id,
quantity*(unit_price-purchase_price) as total_profit,
(unit_price-purchase_price)/purchase_price*100  as profit_margin
from sales_orders 
join purchase_prices
using(product_id)) u
group by product_id
order by product_id