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

with a as (
    select 
        style_id,
        sum(inventory) as sum_inv,
        sum(tag_price * inventory) as GMV
    from product_tb
    group by style_id
),
b as (
    select 
        t1.item_id,
        style_id,
        sales_num,
        sales_price
    from sales_tb t2
    left join product_tb t1
    on t2.item_id = t1.item_id
)
select 
    a.style_id,
    round(sum(sales_num) * 100/(max(sum_inv)-sum(sales_num)),2),
    round(sum(sales_price) * 100/max(GMV),2)
from a
left join b
on a.style_id = b.style_id
group by style_id;