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

关键：分组聚合。关于平均销售单价，用加权平均更严谨，如下代码注释部分。

select s.product_id product_id
,round(sum((unit_price-purchase_price)*quantity),2) total_profit
# ,round((sum(unit_price*quantity)/sum(quantity)/avg(purchase_price)-1)*100,2) profit_margin
,round((avg(unit_price)/avg(purchase_price)-1)*100,2) profit_margin
from sales_orders s 
left join purchase_prices p on s.product_id=p.product_id
where year(order_date)=2023
group by s.product_id
order by s.product_id