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

with tiaojian as (
select
item_id,
date_format(sales_date,"%Y%m"),
sum(sales_num) as pt,
sum(sales_price) as py
from sales_tb
where
date_format(sales_date,"%Y%m")=202111
group by item_id,date_format(sales_date,"%Y%m")
)

select
p.style_id,
round(
(
sum(t.pt)/(sum(p.inventory)-sum(t.pt)))*100,2),
round(
(
sum(t.py)/sum(p.tag_price*p.inventory))*100,2)
from  tiaojian t inner join product_tb p 
on t.item_id=p.item_id
group by p.style_id
order by p.style_id