select a1.product_id,
sum((a1.unit_price-a2.purchase_price)*a1.quantity) as total_profit,
round((avg((a1.unit_price-a2.purchase_price)/a2.purchase_price))*100,2) as profit_margin
from sales_orders as a1
left outer join
purchase_prices as a2 on a1.product_id=a2.product_id
where year(a1.order_date)=2023
group by a1.product_id