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

步骤

1、连接3张表

2、筛选90天内的订单

3、ROW_NUMBER() over(PARTITION BY df2.product_id,df1.uid order by df1.event_time asc) as "rk"

查出购买每个产品的用户 当前订单是第几次购买

4、round(count(distinct case when rk >=2 then uid end)/count(distinct uid),3) as "复购率"

group by product_id

购买次数>=2的用户数 / 购买过的用户数 按产品id分组

SELECT 
product_id,
#count(distinct uid) as "购买的人数",
#count(distinct case when rk >=2 then uid end) as "购买2次以上的人数",
round(count(distinct case when rk >=2 then uid end)/count(distinct uid),3) as "复购率"
from (
        select
        df1.uid,
        df2.product_id,
        ROW_NUMBER() over(PARTITION BY df2.product_id,df1.uid order by df1.event_time asc) as "rk"
        from tb_order_overall as df1
        left join (
                select
                df1.*,
                df2.tag
                from tb_order_detail as df1
                left join tb_product_info as df2
                on df1.product_id = df2.product_id
        ) as df2
        on df1.order_id = df2.order_id
        where df2.tag = '零食'
        and df1.event_time >= (select date_add(max(event_time), interval -89 day) from tb_order_overall)
) as a
group by product_id
order by 复购率 desc,product_id asc
limit 3