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

with t1 as (
select *
from tb_order_overall
    where datediff((select max(date(event_time)) max_date
from tb_order_overall), date(event_time)) <= 89)

select product_id, round(sum(if((purchase_time >=2), 1, 0)) / unum, 3) repurchase_rate
from
(select product_id, t3.uid, count(1) purchase_time
from tb_order_detail t2
left join t1 t3 using(order_id)
group by product_id, t3.uid) t7
left join
(select product_id, count(distinct t5.uid) unum
from tb_order_detail t4
left join t1 t5 using(order_id)
group by product_id) t6 using(product_id)
left join tb_product_info ti using(product_id)
where ti.tag = '零食'
group by product_id
order by repurchase_rate desc ,product_id
limit 3

注意几个条件：

1. 90天之内，包括当天，所以和最大日期的date_diff应该小于等于89天
2. 注意tag标签是零食类的
3. 只取top3 所以最终结果要limit 3