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

with t1 as (
select (select date(max(event_time)) from tb_order_overall tl) as current_t,
date(event_time) as event_time,
ti.product_id,
td.order_id,
uid
from tb_product_info ti
left join tb_order_detail td on ti.product_id = td.product_id
left join tb_order_overall tl on td.order_id = tl.order_id
where tag='零食'
and status = 1
)
select product_id,
round(count(distinct case when num>=2 then uid else null end )/count(distinct uid),3) as repurchase_rate
from(
select product_id,
uid,
count(order_id) as num
from t1
where datediff(current_t,event_time)<90
group by product_id,
uid
)a 
group by  product_id
order by repurchase_rate desc , product_id
limit 3