题解 | 谁能告诉我为什么不对！
店铺901国庆期间的7日动销率和滞销率
https://www.nowcoder.com/practice/e7837f66e8fb4b45b694d24ea61f0dc9
with
sale_product as (
select
b.uid,
b.order_id,
b.event_time,
c.product_id,
a.shop_id
from
tb_order_overall b
left join tb_order_detail c on b.order_id = c.order_id
left join tb_product_info a on c.product_id = a.product_id
)
select
date_format(dt,'%Y-%m-%d') dt,
sale_rate,
unsale_rate
from
(
select
dt,
round(sale_product_cnt / all_product_cnt, 3) as sale_rate,
round(
(all_product_cnt - sale_product_cnt) / all_product_cnt,
3
) as unsale_rate
from
(
select
'2021-10-02' dt,
shop_id,
count(product_id) as all_product_cnt
from
tb_product_info a
where
date (a.release_time) <= '2021-10-02'
and shop_id = 901
group by 1,2
) t1
left join (
select
shop_id,
count(distinct product_id) as sale_product_cnt
from
sale_product b
where
date (event_time) between '2021-09-26' and '2021-10-02'
and shop_id = 901
group by 1
) t2 on t1.shop_id = t2.shop_id
union all
select
dt,
round(sale_product_cnt / all_product_cnt, 3) as sale_rate,
round(
(all_product_cnt - sale_product_cnt) / all_product_cnt,
3
) as unsale_rate
from
(
select
'2021-10-03' dt,
shop_id,
count(product_id) as all_product_cnt
from
tb_product_info a
where
date (a.release_time) <= '2021-10-03'
and shop_id = 901
group by 1,2
) t1
left join (
select
shop_id,
count(distinct product_id) as sale_product_cnt
from
sale_product b
where
date (event_time) between '2021-09-27' and '2021-10-03'
and shop_id = 901
group by 1
) t2 on t1.shop_id = t2.shop_id
union all
select
dt,
round(sale_product_cnt / all_product_cnt, 3) as sale_rate,
round(
(all_product_cnt - sale_product_cnt) / all_product_cnt,
3
) as unsale_rate
from
(
select
'2021-10-01' dt,
shop_id,
count(product_id) as all_product_cnt
from
tb_product_info a
where
date (a.release_time) <= '2021-10-01'
and shop_id = 901
group by 1,2
) t1
left join (
select
shop_id,
count(distinct product_id) as sale_product_cnt
from
sale_product b
where
date (event_time) between '2021-09-25' and '2021-10-01'
and shop_id = 901
group by 1
) t2 on t1.shop_id = t2.shop_id
) t
#sql练习日常##悬赏#
投递牛客等公司10个岗位 >
