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

select  product_id,round(sum(case when num >=2 then 1 else 0 end)/count(*),3) as repurchase_rate from (
select product_id,uid,count(*) as num
from tb_order_overall join tb_order_detail using(order_id)
join tb_product_info using(product_id)
where datediff((select max(date(event_time)) from tb_order_overall),date(event_time)) <90
and tag = '零食'
group by product_id,uid)t
group by product_id
order by repurchase_rate desc,product_id 
limit 3

嵌套from子查询

先算出每个产品，每个人购买过多少次，然后再计算复购率