题解 | #某宝店铺动销率与售罄率#

with t1 as(
select item_id,sum(sales_num) as cnt,
sum(sales_price) as price
from sales_tb group by item_id order by item_id),

t2 as(
select style_id,
round(100*sum(cnt)/sum(inventory-cnt),2) as 'pin_rate(%)',
round(100*sum(price)/sum((inventory)*tag_price),2) AS
'sell-through_rate(%)'
from product_tb pro join t1 on pro.item_id=t1.item_id group by style_id)

select * from t2 order by style_id