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

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;