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

# 指标：复购率 = 近90天内购买它至少两次的人数 / 购买它的总人数
with today as (
select max(date(event_time))
from tb_order_overall
) -- 当天时间
# 1.筛选出近90天的购买记录,根据product_id,uid求出每个商品被同一用户购买的次数，计算是否复购
# select tpi.product_id,uid,count(*) cnt,if(COUNT(event_time)>1, 1, 0) as repurchase
# from tb_product_info tpi
# inner join tb_order_detail tod
# on tpi.product_id=tod.product_id
# inner join tb_order_overall too
# on tod.order_id=too.order_id
# where date(event_time) between date_sub((select * from today),interval 89 day) 
# and (select * from today)
# and tag='零食'
# group by tpi.product_id,uid

# 2.根据product_id，求出复购用户人数，和购买产品总人数，最后求出复购率：复购用户人数/购买产品总人数
select product_id,round(sum(repurchase)/count(uid),3) repurchase_rate
from (
  select tpi.product_id,uid,count(*) cnt,if(COUNT(event_time)>1, 1, 0) as repurchase
  from tb_product_info tpi
  inner join tb_order_detail tod
  on tpi.product_id=tod.product_id
  inner join tb_order_overall too
  on tod.order_id=too.order_id
  where date(event_time) between date_sub((select * from today),interval 89 day) 
  and (select * from today)
  and tag='零食'
  group by tpi.product_id,uid
) as t
group by product_id
order by repurchase_rate desc,product_id
limit 3