拆解题目

2021年11月每天新用户的次日留存率

次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。
如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序。

求什么

每天：以天聚合

次日留存率=第二天有活跃的新用户数量/新用户数量

如果，下次活跃时间，和首次活跃相差1天，即为第二天有活跃
第二天有活跃的新用户数量=sum（if（datediff（）=1,1,0）

筛选：2021年11月，新用户

where left(date,7)='2021-10'
where (uid,date) in(select uid,min(date) first_day from

输出什么

date，留存率（uv_left_rate）

步骤

概览：

数据预处理：uid，date并到一列（union），去重，日期格式date（）
求next_day：标注每个用户下一次活跃时间
筛选条件：新用户(uid,min(date))、时间范围
计算：首次活跃日期聚合，计算next_day-首次活跃日期=1的人/该日新用户量 sum(if())/count()

数据预处理

凡计算留存，一般涉及参数：uid,date（活跃时间）——活跃时间表
uid,date 去重：用户可能一天内多次访问
本题因in_time，out_time跨天都算活跃，因此需要把两列，union 拼到一列

代码

with t0 as(
    select distinct
    uid,date(in_time) dt
    from tb_user_log
    union
    select distinct
    uid,date(out_time) dt
    from tb_user_log
),

每个用户下一次活跃时间

lead（）over（）

以每个用户分区，按时间升序排列，下一个日期

得到：

筛选：新用户、时间

使用where 筛出每个用户的首日，及首日后下一次活跃日期

再筛选2021-10月的

得到：

计算：下一次活跃时间-首次活跃时间=1的人/该日新用户量

下次活跃时间，和首次活跃相差1天，即为第二天有活跃

完整代码

with t0 as(
    select distinct
    uid,date(in_time) dt
    from tb_user_log
    union
    select distinct
    uid,date(out_time) dt
    from tb_user_log
),
t1 as(
    select
    *,lead(dt,1)over(partition by uid order by dt) next_day
    from t0
)
select
dt
,round(sum(if(datediff(next_day,dt)=1,1,0))/count(1),2) uv_left_rate
from t1
where (uid,dt) in(select uid,min(dt) first_day from t1
group by 1)
and left(dt,7)='2021-11'
group by 1
order by 1

扩展：多日留存率（3日/7日）

3日留存率

sum(if(datediff(next_day,dt)<=3,1,0))/count(1) uv_left_rate

下一次活跃时间和首次相差3天以内

7日留存率

sum(if(datediff(next_day,dt)<=7,1,0))/count(1) uv_left_rate

下一次活跃时间和首次相差7天以内

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