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

# max(time) timestampdiff(day, event_time, max(time)) < 90
# group by product_id,uid
# not in status = 0,2 . id
# 窗口函数 count(*) 算 pid.uid 内有多少个  ->  开窗去算 pid 内的满足要求的uid 有多少个 ->  avg() over(pid)

select 
    distinct product_id, round( avg( if(count(event_time) > 1,1,0) ) over(partition by product_id)   , 3) repurchase_rate
from 
tb_order_overall a 
left join tb_order_detail b using(order_id)
left join tb_product_info c using(product_id)
where  timestampdiff(day,event_time, (select  max(event_time) from tb_order_overall)) < 90 and  a.order_id not in (select order_id from tb_order_overall where status != 1) and tag = '零食'
group by product_id, uid
order by repurchase_rate desc, product_id asc limit 3

借用 sql 159 的思路 利用开窗函数来实现