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

#近90天各商品各用户重复购买次数（大于1次）
with t as(
select product_id,count(case when num>1 then uid else null end) as cnt
from(
select td.product_id,uid,
    count(
    case when date(event_time)>=(select date_sub(max(date(event_time)),interval 89 day) from tb_order_overall)
    then uid
    else null end
) as num
from tb_order_detail td
join tb_order_overall too on td.order_id = too.order_id
join tb_product_info tp on td.product_id = tp.product_id
where tag='零食'and status =1
group by td.product_id,uid
)a
group by product_id)

#总购买人数
select td.product_id,
round(t.cnt/count(distinct too.uid),3) as repurchase_rate
from tb_order_detail td
join tb_order_overall too
on td.order_id = too.order_id
join tb_product_info tp
on td.product_id = tp.product_id
 join t on t.product_id = td.product_id
where tp.tag='零食'
and too.status =1
group by td.product_id
order by repurchase_rate desc,td.product_id
limit 3

CTE算的是近90天内各商品下重复购买该商品的人数

有些了一个子查询计算购买商品的总人数，再与CTE连接，则可算出复购率