题解 | #店铺901国庆期间的7日动销率和滞销率#

WITH a AS (
    SELECT 
        DATE(event_time) AS dt,
        product_id
    FROM tb_order_overall
    JOIN tb_order_detail USING(order_id)
    JOIN tb_product_info USING(product_id)
    WHERE shop_id = 901
)
SELECT 
    t.dt,
    ROUND(COUNT(DISTINCT a.product_id) / t.onsale_cnt, 3) AS sale_rate,
    ROUND(1 - COUNT(DISTINCT a.product_id) / t.onsale_cnt, 3) AS unsale_rate
FROM (
    SELECT 
        d.dt,
        COUNT(DISTINCT p.product_id) AS onsale_cnt
    FROM (
        SELECT DISTINCT DATE(event_time) AS dt
        FROM tb_order_overall
        WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
    ) d
    JOIN tb_product_info p 
      ON p.shop_id = 901 AND p.release_time <= d.dt
    GROUP BY d.dt
) t
LEFT JOIN a 
    ON a.dt BETWEEN DATE_SUB(t.dt, INTERVAL 6 DAY) AND t.dt
GROUP BY t.dt
ORDER BY t.dt;