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

select product_id,
round(sum(purse)/count(purse),3) as repurchase_rate
from(
select t.product_id as product_id,
if(count(event_time)>1,1,0) as purse
from tb_product_info as t
join tb_order_overall as t1
join tb_order_detail as t2
on t.product_id = t2.product_id
and t1.order_id = t2.order_id
where tag = '零食'
and event_time >= (select date_sub(max(event_time),interval 89 day) from tb_order_overall)
group by uid,t.product_id) as t
group by product_id
order by repurchase_rate desc,product_id
limit 3

三个表连接，然后筛选条件，按照uid和product_id分组，近90天使用date_sub进行筛选，然后统计即可