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

先拉宽表找出每个人，每商品下单次数 记得过滤条件

select
    pi.product_id,
    oo.uid,
    count(1) cnt
from tb_order_overall oo left join tb_order_detail od
on oo.order_id=od.order_id
left join tb_product_info pi on od.product_id = pi.product_id
where status=1 and event_time>(
    SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
        FROM tb_order_overall
    )
and tag='零食'
group by pi.product_id,oo.uid

下单次数>2，即为复购的

总人数直接分组内count，因为子查询中已经group by了，所以此时不用distinct

然后按照题目要求的精度给round()

按招题目要求的顺序来

select
    product_id,
    round(sum(if(cnt>1,1,0))/count(uid),3) repurchase_rate
from (
    select
        pi.product_id,
        oo.uid,
        count(1) cnt
    from tb_order_overall oo left join tb_order_detail od
    on oo.order_id=od.order_id
    left join tb_product_info pi on od.product_id = pi.product_id
    where status=1 and event_time>(
        SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
            FROM tb_order_overall
        )
    and tag='零食'
    group by pi.product_id,oo.uid
)t1
group by product_id
order by repurchase_rate desc ,product_id ;

每日一练，做完总结！

感觉我写的很浪费性能，有没有大佬能帮我优化一下，感谢了！