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

WITH
    b AS (
        select
            item_id,
            sum(sales_num) as num,
            sum(sales_price) as item_GMV
        from
            sales_tb
        group by
            item_id
    ),
    t AS (
        select
            style_id,
            round(100 * sum(num) / (sum(inventory) - sum(num)), 2) as 'pin_rate(%)',
            round(
                100 * sum(item_GMV) / sum(inventory * tag_price),
                2
            ) as 'sell-through_rate(%)'
        from
            product_tb a
            join b on a.item_id = b.item_id
        group by
            style_id
        order by
            style_id
    )
SELECT
    *
FROM
    t;

# 库存细节需要从小类出发！