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

屎山代码，但是思路最简单的了，核心语句就一条，来回切换时间

with t as ( select product_id,event_time

    from tb_product_info

    join tb_order_detail using(product_id)

    join tb_order_overall using(order_id)

    where shop_id='901'

    and status=1   )

select dt, round(sale_rate,3) sale_rate,round(1-sale_rate,3) unsale_rate

from (

select '2021-10-01' dt

    ,count(distinct product_id)/(select count(product_id) from tb_product_info where date(release_time)<='2021-10-01')  sale_rate

from t

where datediff('2021-10-01',event_time) between 0 and 6

union

select '2021-10-02' dt

    ,count(distinct product_id)/(select count(product_id) from tb_product_info where date(release_time)<='2021-10-02')

from t

where datediff('2021-10-02',event_time) between 0 and 6

union

select '2021-10-03' dt

    ,count(distinct product_id)/(select count(product_id) from tb_product_info where date(release_time)<='2021-10-03')

from t

where datediff('2021-10-03',event_time) between 0 and 6   ) total_tab