题解 | #零食类商品中复购率top3高的商品#

with t1 as (select a.product_id,uid,
max(event_time) over () curr_date,
case when datediff(max(event_time) over (),event_time) <= 89 then 1 else 0 end num_1,event_time,status
from tb_product_info a 
left join tb_order_detail b 
on a.product_id = b.product_id 
left join tb_order_overall c 
on b.order_id = c.order_id
where status in (1,2)
and event_time between date_sub((select max(event_time) from tb_order_overall),INTERVAL 89 day) and (select max(event_time) from tb_order_overall)
and tag = '零食'
)
select product_id,
round((count(distinct case when total_num >=2 then uid else null end)-count(distinct case when status = 2 then uid else null end))/count(distinct uid),3) repurchase_rate
from (select product_id,uid,status,
sum(num_1) over (partition by uid,product_id) total_num
from t1) u
group by product_id
order by repurchase_rate desc
limit 3