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

## 分析：单个产品的利润率需要二次运算，先产品平均单价，后再算一次；利润需要一次计算
## with 中算出利润、平均单价，主查询二次计算得单个产品的利润率
with t as(
    select s.product_id,
        sum((s.unit_price-p.purchase_price )*s.quantity )total_profit,
        avg(unit_price)avg_per
    from sales_orders s join purchase_prices p using(product_id)
    group by product_id
)
select t.product_id,round(t.total_profit,2) as total_profit,
    round((avg_per-purchase_price)/purchase_price*100,2) as profit_margin 
from t join purchase_prices using(product_id)
order by product_id