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

# 请统计零食类商品中复购率top3高的商品

select
    product_id,
    # 某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
    round(count(distinct uid) / num, 3) repurchase_rate
from
    (
        select
        #  近90天内购买它至少两次的人数
            product_id,
            uid,
            count(*) repurchase_num
        from
            tb_product_info
            join tb_order_detail using (product_id)
            join tb_order_overall using (order_id)
        where
            datediff (
                (
                    select
                        max(event_time)
                    from
                        tb_order_overall
                ),
                event_time
            ) < 90
            and tag = '零食'
        group by
            product_id,
            uid
        having
            repurchase_num >= 2
    ) a
    right join (
        #  购买它的总人数
        select
            product_id,
            count(distinct uid) num
        from
            tb_product_info
            join tb_order_detail using (product_id)
            join tb_order_overall using (order_id)
        where
            datediff (
                (
                    select
                        max(event_time)
                    from
                        tb_order_overall
                ),
                event_time
            ) < 90
            and tag = '零食'
        group by
            product_id
    ) b using (product_id)
group by
    product_id
order by
    repurchase_rate desc,
    product_id
limit
    3