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

1、理解题意：
①什么是用户“活跃”？根据题目要求“如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过”可知，当用户存在《注册/登录/离开》的《当天》才算用户“活跃”。
②什么算“新用户”？根据题目要求，“当且仅当”活跃“2天”算新用户
③怎么计算“次留存率”？次留存率=第2天持续活跃人数/第1天注册人数
【注意1：第2天“持续”活跃人数，是针对第1天注册过的用户来说的】
【注意2：可从题目给出的案例中（11.04没有新用户，不输出。），可知“持续活跃”2天内才属于“新用户”，才能符合条件（次日+符合新用户条件=只计算连续2天的人数），就算用户活跃了3天，那对于第3天来说他就属于《老用户》，其就不属于“活跃人数”】
【注意3：统计时间范围“2021年11月每天”】

2、解题：
①创建1张用户注册表（初次登录表）
with user_reg
as(
    select uid,
    date(min(in_time)) as first_date
    from tb_user_log
    group by 1
), -- 用户注册表
②创建1张用户活跃表（在初次登录之后，以后每次的登录/离开日期————注意：此处用union排除1个用户1天内既登入又离开的情况）
user_reac 
as(
    select uid,
    date(in_time) as dt
    from tb_user_log

    union

    select uid,
    date(out_time) as dt
    from tb_user_log
) -- 用户活跃表（登录/离开=活跃）

③将2张表进行左连接（保留初次登录表，用户活跃表的uid与其进行关联，其次用户活跃表的“日期-1天”后《！！！这样就能将1个用户首次登录日期＆第2天登录日期拼接到同1行中》，要与初次登陆表中的日期挂钩），然后以“用户注册表”的日期为分组统计基础，计算每天的“次留存率”
select user_reg.first_date,
round(count(user_reac.dt)/count(user_reg.first_date),2)
from user_reg 
left join user_reac
on user_reg.uid = user_reac.uid
and user_reg.first_date = date_sub(user_reac.dt,interval 1 day)
where user_reg.first_date like '2021-11%'
group by 1