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

1. 计算国庆前三天上架商品数量
2. 筛选9-25到10-3这段时间每日销售的产品
3. 连接俩表，通过国庆前三天与event_time相差不超过7天进行连接
4. 计算国庆前三天每日的前七日产品销售种类
5. 计算动销率和滞销率

(select
    dt,
    count(product_id) sum_product
from tb_product_info
left join 
    (select
        distinct date(event_time) dt
    from tb_order_overall
    where date(event_time) between '2021-10-01' and '2021-10-3') t1 on 1
where date_sub(dt, interval 7 day) >= date(release_time) and shop_id = 901
group by dt),
tb_daily_sale as
(select
    date(event_time) sale_dt,
    product_id
from tb_order_detail
left join tb_order_overall using(order_id)
left join tb_product_info using(product_id)
where shop_id = 901 and status = 1 and date(event_time) between '2021-9-25' and '2021-10-3')
select
    dt, 
    round(count(distinct product_id) / avg(sum_product), 3) sale_rate,
    1 - round(count(distinct product_id) / avg(sum_product), 3) unsale_rate
from tb_product_sum
left join tb_daily_sale on datediff(dt, sale_dt) between 0 and 6
group by dt;