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

SELECT style_id,
ROUND(100*SUM(pd_sold)/ SUM(pd_in_sku),2) AS 'pin_rate(%)',
ROUND(100*SUM(GMV_sku)/ SUM(inventory * tag_price),2) AS 'sell_through_rate(%)'
FROM(
    SELECT style_id,item_id,GMV_sku,pd_sold,tag_price,inventory,
    (inventory-pd_sold) pd_in_sku
    FROM (
        SELECT item_id,
        SUM(sales_price) GMV_sku,
        SUM(sales_num) pd_sold
        FROM sales_tb
        GROUP BY item_id
    )t1
    LEFT JOIN product_tb USING(item_id)
)t2
GROUP BY style_id
ORDER BY style_id;