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

select a.product_id, 
       ifnull(round(cnt_2/cnt_total,3),0.000) repurchase_rate
from tb_product_info a
left join (select product_id,
                  count(distinct uid) cnt_total     -- 该产品被几个人购买过
           from tb_order_detail a
           left join tb_order_overall b
           on a.order_id = b.order_id
           GROUP BY product_id) b
on a.product_id = b.product_id     
left join (select product_id,
                  count(distinct uid) cnt_2       -- 再选出买过该产品大于等于2次的人数
           from (select product_id,
                        uid,count(1) cnt       -- 该产品每个人买过的次数
                 from tb_order_detail a
                 left join tb_order_overall b
                 on a.order_id = b.order_id
                 where (DATEDIFF((select max(event_time) from tb_order_overall),date(event_time)) < 90)
                       and status != 2
                 GROUP BY product_id,uid) t
            where cnt >= 2
            group by product_id) c
on a.product_id = c.product_id
where tag = '零食'
order by repurchase_rate desc , product_id
limit 3