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

with t as
(
select 
tod.product_id product_id,tag,uid,date(event_time) dt,
max(date(event_time)) over() max  # 最大日期
from tb_order_overall too
join tb_order_detail tod on too.order_id = tod.order_id
join tb_product_info tpi on tpi.product_id = tod.product_id	
where status = 1 and tag = "零食"
), 

r as(    # 各商品每个用户的购买次数，先执行窗口函数，后distinct
select 
distinct product_id,uid,count(uid) over(partition by product_id,uid) c
from t 
where dt between date_sub(max,interval 89 day) and max )

# p as
# (select product_id,count(uid) m from r where c>=2 group by product_id)
# 把购买次数大于等于2的人数筛选出来连接t，存在复购人数为0需要左连接的情况

select 
product_id,
round(sum(if(c>=2,1,0))/count(uid),3) repurchase_rate
from r 
group by product_id
order by repurchase_rate desc,product_id
limit 3