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

SELECT 
    a.product_id,
    SUM((a.unit_price - b.purchase_price) * a.quantity) AS total_profit,
    ROUND(AVG((a.unit_price - b.purchase_price)/b.purchase_price)*100, 2) AS profit_margin
FROM 
    sales_orders AS a
JOIN 
    purchase_prices AS b ON a.product_id = b.product_id
GROUP BY 
    a.product_id
ORDER BY 
    a.product_id ASC

表连接+窗口函数计算