题解 | 25年得物-潮鞋新品发售后 N 日复购留存矩阵

#首选取出每个用户购买每类商品的首次日期
with first_buy as (
    select user_id, series_id, min(date(pay_time)) as first_buy_date
    from t_order
    group by user_id, series_id
),
#通过first_buy取出的首次购买日期与订单表中每一个订单的日期做差，即可得到每一单是首次购买还是首次购买之后x天购买的
retain_days as (
    select 
        o.user_id, 
        o.series_id, 
        f.first_buy_date, 
        datediff(date(o.pay_time), f.first_buy_date) as retain_day
    from t_order o 
    inner join first_buy f 
        on o.user_id = f.user_id 
       and o.series_id = f.series_id
),
#通过上一步计算出的留存天数(0,1,3,7,14)可得出每种留存天数下的用户数
retain_cnt as (
    select 
        series_id, 
        count(distinct if(retain_day=0, user_id, null)) as first_buyer_cnt,
        count(distinct if(retain_day=1, user_id, null)) as d1_cnt,
        count(distinct if(retain_day=3, user_id, null)) as d3_cnt,
        count(distinct if(retain_day=7, user_id, null)) as d7_cnt,
        count(distinct if(retain_day=14, user_id, null)) as d14_cnt
    from retain_days
    group by series_id
)
#得出首单用户、留存x天的用户数后，即可求出留存率
select 
    r.series_id, 
    s.series_name, 
    first_buyer_cnt, 
    round(d1_cnt / nullif(first_buyer_cnt,0), 2) as d1_rate, 
    round(d3_cnt / nullif(first_buyer_cnt,0), 2) as d3_rate, 
    round(d7_cnt / nullif(first_buyer_cnt,0), 2) as d7_rate, 
    round(d14_cnt / nullif(first_buyer_cnt,0), 2) as d14_rate 
from retain_cnt r 
left join t_series s 
    on r.series_id = s.series_id
order by d7_rate desc, first_buyer_cnt desc, series_id;