#查询出每个商品在 2023 年的利润
#查询出来的数据按照商品 ID 升序排列
select a.product_id,sum(quantity*(unit_price - purchase_price)) as total_profit,round(avg((unit_price-purchase_price)/purchase_price*100),2) as profit_margin
from sales_orders as a
inner join purchase_prices as b
on a.product_id = b.product_id
where date_format(order_date,'%Y') = '2023'
group by a.product_id
order by a.product_id