题解 | #零食类商品中复购率top3高的商品#
零食类商品中复购率top3高的商品
https://www.nowcoder.com/practice/9c175775e7ad4d9da41602d588c5caf3
select
product_id,
round(sum(comt)/count(distinct uid),3) repurchase_rate
from
(
select
product_id,
uid,
if(com>1,1,0) comt
from
(
select
product_id,
uid,
count(*) com
from
(select
a.product_id,
uid
from
tb_product_info a,
tb_order_overall b,
tb_order_detail c
where
a.product_id=c.product_id and b.order_id = c.order_id
and tag = '零食'
and datediff((select max(date(event_time)) from tb_order_overall),date(event_time)) <=89
order by product_id,uid
) t1
group by product_id,uid
order by product_id
) t2
) t3
group by product_id
order by repurchase_rate desc
limit 3;
