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

with tb2 as(
select product_id, uid from
(select *, max(date(event_time))over() dt from tb_order_overall where status=1) tb1
left join tb_order_detail using(order_id)
left join tb_product_info using(product_id)
where tag='零食' and datediff(dt,event_time) between 0 and 89)

select product_id, round(count(if(num>=2,1,null))/count(num),3) a
from(
select product_id, uid, count(uid) num
from tb2 
group by product_id, uid) as tb3
group by product_id
order by a desc, product_id
limit 3

#思路：
#①三表连接，加入日期筛选条件（90天内）;
#②根据连接后的表，根据商品id、uid分组计算购买次数；
#③再计算每种商品分组下，购买2次以上的人数占总人数的比例即可。