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

with tm as (
    select user_id,series_id,date(min(pay_time)) od
    from t_order
    group by user_id,series_id
),
tt as(select t.user_id,tm.series_id,od,pay_time,case when date_add(od,interval 1 day)=date(pay_time)and t.series_id = tm.series_id then 1 else 0 end as d1,case when date_add(od,interval 3 day)=date(pay_time)and t.series_id = tm.series_id then 1 else 0 end as d3,case when date_add(od,interval 7 day)=date(pay_time)and t.series_id = tm.series_id then 1 else 0 end as d7,case when date_add(od,interval 14 day)=date(pay_time)and t.series_id = tm.series_id then 1 else 0 end as d14
from t_order t
left join tm on t.user_id=tm.user_id
)
select tt.series_id,series_name,count(distinct user_id) first_buyer_cnt,round(count(distinct case when d1 = 1 then user_id end)/count(distinct user_id),2) d1_rate,round(count(distinct case when d3 = 1 then user_id end)/count(distinct user_id),2) d3_rate,round(count(distinct case when d7 = 1 then user_id end)/count(distinct user_id),2) d7_rate,round(count(distinct case when d14 = 1 then user_id end)/count(distinct user_id),2) d14_rate
from tt
left join t_series t on tt.series_id=t.series_id
group by tt.series_id,series_name
order by d7_rate desc,first_buyer_cnt desc,tt.series_id