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

首先，将题目中所需要的产品id，用户id，购买时间，时间期限窗口等关键信息，做临时表

1. 将最近的日期设置为当前日期，利用窗口函数做单独列名
2. 找出90天内的时间期限，作为统计日期起点，运用date_sub 函数

with a as
(
    select t.product_id
        , o.uid
        , o.event_time
        , o.total_amount
        , max(event_time) over() as cur_dt
        , date_sub(max(event_time)over(), interval 89 day) as start_dt
    from tb_product_info t
    left join tb_order_detail d 
    on t.product_id = d.product_id
    left join tb_order_overall o 
    on d.order_id= o.order_id
    where o.status = 1 and t.tag ='零食'
    order by product_id,uid
)

接着，需要找到对应的数据信息，分母为去重后的购买该商品的用户数，注意去重的使用

其次，需要统计出在时间区间里，购买2次以上的用户数作为复购用户，分子

select product_id
# , count(distinct uid) as num
# , sum(if(buy_times>=2,1,0)) as re_num
,round(sum(if(buy_times>=2,1,0))/count(distinct uid), 3) as re_rate
from 
(
select product_id
    ,uid 
    , count(*) as buy_times
 from a
 where event_time between start_dt and cur_dt 
 group by product_id,uid
)b
group by product_id
order by 2 desc,1
limit 3

最后的代码可以表示为

with a as
(
    select t.product_id
        , o.uid
        , o.event_time
        , o.total_amount
        , max(event_time) over() as cur_dt
        , date_sub(max(event_time)over(), interval 89 day) as start_dt
    from tb_product_info t
    left join tb_order_detail d 
    on t.product_id = d.product_id
    left join tb_order_overall o 
    on d.order_id= o.order_id
    where o.status = 1 and t.tag ='零食'
    order by product_id,uid
)
select product_id
# , count(distinct uid) as num
# , sum(if(buy_times>=2,1,0)) as re_num
,round(sum(if(buy_times>=2,1,0))/count(distinct uid), 3) as re_rate
from 
(
select product_id
    ,uid 
    , count(*) as buy_times
 from a
 where event_time between start_dt and cur_dt 
 group by product_id,uid
)b
group by product_id
order by 2 desc,1
limit 3