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

# 第二次练习  4/3  9：47~
# 字段：dt 、 uv_left_rate
# 统计每天新用户的次日留存率

# 注意：跨天的也算为活跃，代表out_time也作为活跃记录日
# tb0：将in_time 和 out_time 组合
with tb0 as (
    select distinct uid,date(in_time) as in_time
    from tb_user_log
    union 
    select distinct uid,date(out_time) as in_time
    from tb_user_log
),
# 注意2：合并in_time 和 out_time 的时候需要利用date公式转化为日期格式先

# tb1：标记新用户 || 日期偏移 || 标记次日是否留存
tb1 as(
    select distinct uid , date(in_time) as in_date , 
    if(min(in_time)over(partition by uid) = in_time,1,0) as if_new,
    date(lead(in_time,1)over(partition by uid order by in_time asc)) as next_dt,
    # 同时标记 first_day 和 next_day 是否只相差一天()
    if(timestampdiff(day,date(in_time),date(lead(in_time,1)over(partition by uid order by in_time asc)))=1,1,0) as if_back
    # 当if_new 和 if_back都为真的时候即为我们要统计的新用户留存数
    from tb0
)
# tb2：统计新用户 次日留存数、 新用户数
select in_date, round(sum(if(if_new=1 and if_back=1,1,0))/sum(if_new),2) as uv_left_rate
# 新用户数即if_new = 1 的数量，新用户次日留存数即 if_new 且 if_back数量
#,sum(if(if_new=1 and if_back=1,1,0)) as back_users , sum(if_new) as new_users
from tb1 
where date_format(in_date,'%Y-%m') = '2021-11'
group by in_date
having sum(if_new) != 0 

# 如果in_time-进入时间和out_time-离开时间跨天，在两天里都记为该用户活跃过，结果按日期升序。
# 注意：跨天的也算为活跃，代表out_time也作为活跃记录日