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

with t as (
  select
    a.product_id,
    event_time,
    release_time,
    shop_id
  from
    tb_order_detail a
    left join tb_order_overall b on a.order_id = b.order_id
    left join tb_product_info c on a.product_id = c.product_id
  where
    status = 1
)
select
  dt,
  round(sale_cnt / total_cnt, 3) as sale_rate,
  round(1 - sale_cnt / total_cnt, 3) as unsale_rate
from
  (
    select
      t1.dt,
      count(distinct t2.product_id) as sale_cnt,
      count(distinct t3.product_id) as total_cnt
    from
      (
        select
          distinct date(event_time) dt
        from
          t
      ) t1
      left join (
        select
          product_id,
          date(event_time) dt
        from
          t
        where
          shop_id = 901
      ) t2 on datediff(t1.dt, t2.dt) between 0 and 6
      left join(
        select
          product_id,
          date(release_time) as release_dt
        from
          t
        where
          shop_id = 901
      ) t3 on t3.release_dt < t1.dt
    group by
      t1.dt
  ) tt
  where dt between '2021-10-01' and '2021-10-03'
  order by dt