题解 | #零食类商品中复购率top3高的商品(时间筛选)#

select product_id,round(sum(if(c>=2,1,0))/count(*),3) repurchase_rate
from (
select distinct tb_order_detail.product_id ,uid,
count(*) over(partition by tb_order_detail.product_id,uid ) c
from tb_order_detail  
join tb_product_info on tb_product_info.product_id = tb_order_detail.product_id 
join tb_order_overall on tb_order_overall.order_id = tb_order_detail.order_id
where datediff((select max(date(event_time)) from tb_order_overall),date(event_time)) < 90 and 
tag = '零食') t1
group by product_id
order by repurchase_rate desc,product_id 
limit 3

时间范围：

1 datediff((select max(date(event_time)) from tb_order_overall),date(event_time)) < 90

2 event_time >= (SELECT DATE_SUB(MAX(event_time), INTERVAL 89DAY) FROM tb_order_overall

思路：三表链接选取90天内标签为零食的数据，计算每个产品被每个uid购买的次数（窗口函数），根据商品分组计算购买商品的总uid数和购买2次及以上的次数