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

 # 请你统计每款的动销率（pin_rate，有销售的SKU数量/在售SKU数量）
# 售罄率（sell-through_rate，GMV/备货值，备货值=吊牌价*库存数）
# select
#     style_id,
#     tag_price,
#     inventory,
#     sales_num,
#     sales_price
# from
select
    style_id,
    # inventory,
    # saled_num,
    round(saled_num / (inventory - saled_num) * 100, 2) as 'pin_rate(%)',
    # beihuozhi,
    # GMV,
    round(GMV / beihuozhi * 100, 2) as 'sell-through_rate(%)'
from
    (
        select
            style_id,
            sum(inventory) as inventory,
            sum(tag_price * inventory) as beihuozhi
        from
            product_tb
        group by
            style_id
    ) as t2
    join (
        select
            style_id,
            sum(sales_num) as saled_num,
            sum(sales_price) as GMV
        from
            product_tb
            join sales_tb using (item_id)
        group by
            style_id
    ) as t1 using (style_id)
order by
    style_id