题解 | 了解 2023 年全年所有商品的盈利情况
了解 2023 年全年所有商品的盈利情况
https://www.nowcoder.com/practice/05cbbb8662c14b46a15cbcb8993d9277
with temp as (
select
so.order_id,
pp.product_id,
pp.purchase_price,
so.quantity,
so.unit_price
from sales_orders so
inner join purchase_prices pp
on pp.product_id = so.product_id
where year(so.order_date) = 2023
)
select
tp.product_id,
-- 总利润不变
sum((tp.unit_price - tp.purchase_price) * tp.quantity) as total_profit,
round(
(avg(tp.unit_price) - max(tp.purchase_price))
/ max(tp.purchase_price) * 100,
2
) as profit_margin
from temp tp
group by
tp.product_id
order by
tp.product_id asc;
查看9道真题和解析