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

写的很差，暴力解出来的，注意1819行的窗口函数吧



select 
total_num.product_id,
round(count(two_num.uid)/total_num.uid,3) repurchase_rate
from
(
select 
a.product_id,
count(distinct a.uid) uid
from
(
select
tod.product_id,
too.uid,
if(datediff(max(too.event_time) over(),
too.event_time)<=89,1,0) ninteen_day
from
tb_order_detail tod left join tb_order_overall too
on tod.order_id=too.order_id
) a
where a.ninteen_day=1
group by a.product_id
) total_num
left join 
(
select 
a.product_id,
a.uid,
count(a.event_time)
from
(
select
tod.product_id,
too.uid,
too.event_time,
if(datediff(max(too.event_time) over(),
too.event_time)<=89,1,0) ninteen_day
from
tb_order_detail tod left join tb_order_overall too
on tod.order_id=too.order_id
) a
where a.ninteen_day=1
group by a.product_id,a.uid
having count(a.event_time)>=2
) two_num
on total_num.product_id=two_num.product_id
left join tb_product_info on total_num.product_id=tb_product_info.product_id
where tb_product_info.tag='零食'
group by total_num.product_id
order by repurchase_rate desc,total_num.product_id
limit 3