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

SELECT
    s.product_id,
    SUM((s.unit_price-p.purchase_price)*s.quantity) AS total_profit,
    ROUND((AVG(s.unit_price)-AVG(p.purchase_price))/p.purchase_price * 100,2) AS profit_margin
FROM
    sales_orders AS s
    INNER JOIN
    purchase_prices AS p ON s.product_id=p.product_id   
WHERE
    YEAR(s.order_date)=2023
GROUP BY
    s.product_id
ORDER BY
    s.product_id;