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

1. 首先合并tb_product_info，tb_order_overall，tb_order_detail拿到一张详细的带有product_id,uid,order_id,及event_time的表格

select * 
    from tb_product_info 
    inner join tb_order_detail
    using(product_id)
    inner join tb_order_overall
    using(order_id)

2. 筛选标签tag为零食且订单状态为1的近90天的订单

select *
    from tb_product_info 
    inner join tb_order_detail
    using(product_id)
    inner join tb_order_overall
    using(order_id)
    where status = 1 
    and tag = '零食' 
    and event_time >= (
       select date_sub(max(event_time), interval 89 day) #订单中的最大日期-89 = 近90天
             FROM tb_order_overall 
    ) 

3. 按照uid,product_id进行统计找出每个product_id里有产生复购的uid

select product_id, uid, 
        case when count(uid) > 1 THEN 1
        ELSE 0 
        END repurchase_count 
        from tb_product_info 
        inner join tb_order_detail
        using(product_id)
        inner join tb_order_overall
        using(order_id)
        where status = 1 and tag = '零食' and event_time >= (
            select date_sub(max(event_time), interval 89 day) FROM tb_order_overall
        ) GROUP BY uid, product_id

4. 按照product_id再汇总，计算每个商品的复购率并按复购率倒序、商品ID升序排序， 找出TOP3高的商品

select product_id, 
    round(sum(repurchase_count)/count(distinct uid),3) as repurchase_rate
    from (
        select product_id, uid, 
        case when count(uid) > 1 THEN 1
        ELSE 0 
        END repurchase_count 
        from tb_product_info 
        inner join tb_order_detail
        using(product_id)
        inner join tb_order_overall
        using(order_id)
        where status = 1 and tag = '零食' and event_time >= (
            select date_sub(max(event_time), interval 89 day) FROM tb_order_overall
        ) GROUP BY uid, product_id
    ) temp 
    GROUP BY product_id
    ORDER BY repurchase_rate DESC, product_id
    LIMIT 3