题解 | #2021年11月每天新用户的次日留存率#

# 二刷：通用方法 22:25 ~ 22:48   23min
with tb0 as (
    select distinct uid,date(in_time) as in_date from tb_user_log
    union 
    select distinct uid,date(out_time) as in_date from tb_user_log
),
# 1.自连接
tb1 as(
    select a.uid,a.in_date as time1,b.in_date as time2,
    #  标记新用户
    if(min(a.in_date)over(partition by a.uid order by a.in_date) = a.in_date,1,null) as if_new
    from tb0 a left join tb0 b on a.uid = b.uid
),
# 2.计算时间差
tb2 as(
    select uid,timestampdiff(day,time1,time2) as diff_time,time1,time2,if_new
    from tb1
    # 筛选新用户  且 2021年11月
    where if_new = 1
)
# 3.计数符合天数差的作为n日留存数
select time1,
# count(distinct if(diff_time = 1 ,uid,null)),
# count( distinct uid),
round(count(distinct if(diff_time = 1 ,uid,null))/count(distinct uid),2)
from tb2
# 时间筛选
where date_format(time1,'%Y%m') = '202111'
group by time1
having count(distinct uid) != 0 
order by time1