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

-- 逻辑拆解：筛选条件-2023年，所求内容-利润，分组对象-每个商品
SELECT 
    t1.product_id,
    SUM((t2.unit_price - t1.purchase_price) * t2.quantity) total_profit,
    ROUND((AVG(t2.unit_price) - AVG(t1.purchase_price)) / AVG(t1.purchase_price) * 100,2) profit_margin
FROM purchase_prices t1
LEFT JOIN sales_orders t2
ON t1.product_id = t2.product_id
AND t2.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY t1.product_id
ORDER BY t1.product_id