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

with a2 as(
with a1 as(
select t1.product_id,uid
from
tb_order_detail t1
left join
tb_order_overall t2
on t1.order_id = t2.order_id
left join 
tb_product_info t3
on t1.product_id = t3.product_id
where datediff( (select max(date(event_time))
from
tb_order_overall),event_time) <= 89
and tag = '零食')
select product_id,uid,count(*) each_cnt
from a1
group by product_id,uid)
select product_id,round(sum(if(each_cnt >=2,1,0))/count(distinct uid),3) repurchase_rate
from a2
group by product_id
order by repurchase_rate desc,product_id
limit 3;