题解 | #零食类商品中复购率top3高的商品#
零食类商品中复购率top3高的商品
https://www.nowcoder.com/practice/9c175775e7ad4d9da41602d588c5caf3
# 零食类商品订单明细,订单明细表tb_order_detail为底表 with temp as ( select uid, order_id, t.product_id, dt from ( select uid, tb_order_detail.order_id, product_id, date_format(event_time,'%Y-%m-%d') as dt from tb_order_detail left join tb_order_overall on tb_order_overall.order_id = tb_order_detail.order_id where status = 1 )t left join tb_product_info on tb_product_info.product_id = t.product_id where tag='零食' and datediff((select max(event_time) from tb_order_overall),dt)<90 ) select tb_product_info.product_id, ifnull(repurchase_rate,0.000) as repurchase_rate from tb_product_info left join( select t3.product_id, round(re_person/sum_person,3) as repurchase_rate from( # 2.每个被复购的零食的购买客户数 select product_id, count(distinct uid) as re_person from ( # 1. 用户复购的零食:用户零食对应表 select uid, product_id from temp group by uid, product_id having count(order_id) > 1 )t1 group by product_id )t2 left join( # 3. 每个零食的购买总人数 select product_id, count(distinct uid) as sum_person from temp group by product_id )t3 on t2.product_id = t3.product_id )t4 on tb_product_info.product_id = t4.product_id where tag='零食' order by repurchase_rate desc, tb_product_info.product_id limit 3
多表join的复杂业务逻辑需要注意:
- 捋清楚join过程,根据题目要求选择底表是什么。在join过程是否需要distinct?
- 先构建明细大表,再进行select
- 不要怕麻烦,只是代码很长,问题并不复杂。
- 清晰的解题思路最重要,思路捋清楚之后,代码只是填空!