题解 | #零食类商品中复购率top3高的商品#
零食类商品中复购率top3高的商品
https://www.nowcoder.com/practice/9c175775e7ad4d9da41602d588c5caf3
select t4.product_id, if(t3.nums1 is null ||t4.nums=0,0.000,round(t3.nums1/t4.nums, 3)) 'repurchase_rate' from ( select product_id, count(distinct uid) 'nums' from ( select tpi.tag, tpi.product_id, too.uid from tb_product_info tpi join tb_order_detail tod on tpi.product_id = tod.product_id and tpi.tag = '零食' join tb_order_overall too on tod.order_id = too.order_id and datediff ( ( select max(date (event_time)) from tb_order_overall ), date (too.event_time) ) < 90 and too.status != 2 ) t1 group by product_id ) t4 left join ( select product_id, count(uid) 'nums1' from ( select product_id, uid, count(*) 'nums' from ( select tpi.tag, tpi.product_id, too.uid from tb_product_info tpi join tb_order_detail tod on tpi.product_id = tod.product_id and tpi.tag = '零食' join tb_order_overall too on tod.order_id = too.order_id and datediff ( ( select max(date (event_time)) from tb_order_overall ), date (too.event_time) ) < 90 and too.status != 2 ) t1 group by t1.product_id, t1.uid having nums > 1 ) t2 group by t2.product_id ) t3 on t4.product_id = t3.product_id order by repurchase_rate desc limit 3;