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

SELECT 
    dt,round(cnt/product_cnt,3)sale_rate,round(1-cnt/product_cnt,3)unsale_rate
FROM
    (SELECT 
        distinct date(event_time)dt,
        (SELECT count(distinct product_id)
        FROM tb_order_overall 
            JOIN tb_order_detail USING(order_id)
            JOIN tb_product_info USING(product_id)
        WHERE shop_id=901 and datediff(date(t.event_time),date(event_time)) between 0 and 6)cnt,
        (SELECT count(product_id) 
         FROM tb_product_info 
         WHERE shop_id=901)product_cnt
    FROM tb_order_overall t
    WHERE date(event_time) between '2021-10-01' and '2021-10-03')sale_cnt
ORDER BY dt;