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

# 零食类商品订单明细,订单明细表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的复杂业务逻辑需要注意：

1. 捋清楚join过程，根据题目要求选择底表是什么。在join过程是否需要distinct？
2. 先构建明细大表，再进行select
3. 不要怕麻烦，只是代码很长，问题并不复杂。
4. 清晰的解题思路最重要，思路捋清楚之后，代码只是填空！