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

SELECT
style_id,
ROUND(SUM(i_sales_num_total)/(SUM(i_inventory_total) - SUM(i_sales_num_total)) * 100, 2) AS 'pin_rate(%)',
ROUND(SUM(i_sales_price_total)/SUM(i_tag_price) * 100, 2) AS 'sell-through_rate(%)'

FROM(
SELECT
style_id,
item_id,
SUM(sales_num) AS i_sales_num_total,
SUM(DISTINCT inventory) AS i_inventory_total,
SUM(sales_price) AS i_sales_price_total,
SUM(DISTINCT tag_price * inventory) AS i_tag_price

FROM product_tb
JOIN sales_tb
    USING(item_id)

WHERE LEFT(sales_date, 7) = '2021-11'

GROUP BY style_id, item_id) AS t1

GROUP BY style_id