解题思路

1. 先将三个表格连在一起，筛选出零食标签和时间在90天以内的
2. dense_rank统计总购买人数（相同用户去重），lead筛选出购买两次及以上的用户
3. limit 3不要忘记了，审题仔细
4. 啊！我发现这个题解有误，lead筛选出购买两次及以上的用户万一有重复的就错了，牛客网实例都是没重复的所以通过了。害难受呜呜，思考那么久！但是又很开心，自己想了80%正确的答案，又发现了那20%的错误！再接再厉加油！
5. 正确的代码写出来放在第二个了，第一个就当教训放在那里，提醒自己多思考！加油！
6. 更新一下，我将单个商品拎出来，分别计算，再union起来，在workbench是可以运行的，但是牛客不行，毕竟不符合题意，emm怎么说呢，反正题意不同，如果是算所有商品在近九十天的，那么牛客解答没问题，如果是算单个商品自己近九十天的，那就有问题了，还是要单个商品拎出来一个一个算.为了让自己以后回过来看好理解，举个例子：

①101 8001 2021.10.20 ②101 8001 2021.12.20 ③102 8003 2021.5.20 ④103 8003 2021.4.20

如果按照牛客题意，那么我们的8003是没有回购率的，毕竟12.20日的近九十天是9.20-12.20的，而8003不在，但如果是单个商品的回购率，那么就是8001的近九十天是9.20-12.20的，而8003的近九十天是3.20-5.20的，那就可以分别算回购率了！以后出去实习，一定要问清楚，不要稀里糊涂就做啦！

部分错误代码

select 
	product_id,
    round(sum(if(uid=uidd,1,0))/max(total),3) as repurchase_rate
from(
select 
	product_id,
    uid,
	dense_rank() over(partition by product_id order by uid) total,
    lead(uid,1) over (partition by product_id order by uid) uidd
from
(select 
	tod.product_id,
    uid,
    event_time
from tb_order_detail tod
left join tb_order_overall too
using(order_id)
left join tb_product_info tpi
using(product_id)
where tag='零食' and event_time>=(
select date_sub(max(event_time),interval 89 day)
from tb_order_overall
)) as t1)as t2
group by product_id
order by repurchase_rate desc,product_id
limit 3

正确代码

select 
	product_id,
    round(sum(repeatper)/count(repeatper),3) as repurchase_rate
from(
select 
	tod.product_id,
    uid,
    if(count(event_time)>1,1,0) as repeatper
from tb_order_detail tod
left join tb_order_overall too
using(order_id)
left join tb_product_info tpi
using(product_id)
where tag='零食' and event_time>=(
select date_sub(max(event_time),interval 89 day)
from tb_order_overall)
group by product_id,uid) as t1
group by product_id
order by repurchase_rate desc,product_id
limit 3

union部分

(select 
	product_id,
    round(sum(repeatper)/count(repeatper),3) as repurchase_rate
from(
select 
	tod.product_id,
    uid,
    if(count(event_time)>1,1,0) as repeatper
from tb_order_detail tod
left join tb_order_overall too
using(order_id)
left join tb_product_info tpi
using(product_id)
where tag='零食' and event_time>=(
select date_sub(max(event_time),interval 89 day)
from tb_order_overall) and product_id=8001
group by product_id,uid) as t1
union
select 
	product_id,
    round(sum(repeatper)/count(repeatper),3) as repurchase_rate
from(
select 
	tod.product_id,
    uid,
    if(count(event_time)>1,1,0) as repeatper
from tb_order_detail tod
left join tb_order_overall too
using(order_id)
left join tb_product_info tpi
using(product_id)
where tag='零食' and event_time>=(
select date_sub(max(event_time),interval 89 day)
from tb_order_overall) and product_id=8002
group by product_id,uid) as t1)
union
(select 
	product_id,
    round(sum(repeatper)/count(repeatper),3) as repurchase_rate
from(
select 
	tod.product_id,
    uid,
    if(count(event_time)>1,1,0) as repeatper
from tb_order_detail tod
left join tb_order_overall too
using(order_id)
left join tb_product_info tpi
using(product_id)
where tag='零食' and event_time>=(
select date_sub(max(event_time),interval 89 day)
from tb_order_overall) and product_id=8003
group by product_id,uid) as t1)
union
(select 
	product_id,
    round(sum(repeatper)/count(repeatper),3) as repurchase_rate
from(
select 
	tod.product_id,
    uid,
    if(count(event_time)>1,1,0) as repeatper
from tb_order_detail tod
left join tb_order_overall too
using(order_id)
left join tb_product_info tpi
using(product_id)
where tag='零食' and event_time>=(
select date_sub(max(event_time),interval 89 day)
from tb_order_overall) and product_id=8004
group by product_id,uid) as t1)
order by repurchase_rate desc,product_id
limit 3