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

select A.product_id, round(sum(if(A.pur_tm >=2, 1,0))/count(pur_tm),3) as repurchase_rate from
(select tb_order_detail.product_id, tb_order_overall.uid, count(date(event_time)) as pur_tm from
	tb_order_detail left join tb_product_info 
    on tb_order_detail.product_id = tb_product_info.product_id
    left join tb_order_overall
    on tb_order_detail.order_id =  tb_order_overall.order_id
where tag = "零食" and
date(event_time) <= (select max(date(event_time)) from tb_order_overall) 
and date(event_time) >= (select date_add((select max(date(event_time)) from tb_order_overall), interval "-89" day))
and status = 1
group by tb_order_detail.product_id, tb_order_overall.uid) as A
group by product_id
order by repurchase_rate desc, A.product_id asc
limit 3;

step 1: 求出每件产品被不同用户买的次数的表格

限制条件： 近90天、tag为零食、status需要购买

step2: 在上表基础上，计算每个产品的复购率（买两次及其以上的用户数/购买产品的所有用户数）