题解 | 了解 2023 年全年所有商品的盈利情况
了解 2023 年全年所有商品的盈利情况
https://www.nowcoder.com/practice/05cbbb8662c14b46a15cbcb8993d9277
关键：分组聚合。关于平均销售单价，用加权平均更严谨，如下代码注释部分。
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