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

SELECT
    dt,
    ROUND(COUNT(DISTINCT IF(DATEDIFF(dt,event_time) BETWEEN 0 AND 6,product_id,NULL))/total,3) sale_rate,
    ROUND(1-COUNT(DISTINCT IF(DATEDIFF(dt,event_time) BETWEEN 0 AND 6,product_id,NULL))/total,3) unsale_rate
FROM(
    SELECT product_id, COUNT(*) OVER() total FROM tb_product_info WHERE shop_id=901
) pi
INNER JOIN
    tb_order_detail od USING(product_id)
INNER JOIN
    tb_order_overall oo USING(order_id)
JOIN(
    SELECT DATE(event_time) dt FROM tb_order_overall WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
) time
GROUP BY
    dt, total
ORDER BY
    dt