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

# 动销率=每个货号已经销售的数量/（库存-已经销售的数量）
# 售罄率 = GMV（即总销售额）/（吊牌价*库存数量）
with t1 as (
    select item_id, sum(sales_num) as sales_num, sum(sales_price) as gmv
    from sales_tb
    group by item_id
),
t2 as (
    select style_id, sum(tag_price*inventory) as fenmu, sum(inventory) as total_num
    from product_tb
    group by style_id
),
t3 as (
    select a.style_id, sum(b.sales_num) as sale_num, sum(b.gmv) as total_gmv
    from product_tb as a
    right join t1 as b
    on a.item_id = b.item_id
    group by a.style_id
)
select t2.style_id, 
ROUND(t3.sale_num/(t2.total_num-t3.sale_num)*100, 2) as pin_rate,
ROUND(t3.total_gmv/t2.fenmu*100 ,2) as sell_through_rate
from t2
left join t3
on t2.style_id = t3.style_id
order by t2.style_id;