问题：统计2021年11月每天新用户的次日留存率（保留2位小数）

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

1.我们先看到注中如果跨天算两天都活跃了，使用如下代码表示出用户和其活跃日期。

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

我们这里考虑使用union而不是union all，原因在于，同一用户可能一天活跃多次或者进入和出去的时候在同一天，都只用算当天活跃就好，我们不用知道其次数，union 帮我们去重了，也方便了我们后续的计算。

2.我们要求新用户的次日留存率，那我们需要对用户的活跃日期进行排序进行。

select *,rank()over(partition by uid order by dt) as rk
from 
(select uid,date(in_time) as dt from tb_user_log
 union 
 select uid,date(out_time) as dt from tb_user_log
) as u

代码结果如下

alt

3.将2中的表作为临时表t。我们求次日留存率，需要两个表一个t作为t1一个作为t2 ，使用t1 left join t2 ，连接条件是id相同且t2的日期比t1多一天。使用count(t2.uid)/count（t1.uid)即可得出结果。

这里我们再筛选出新用户（rk=1)

日期 left(t1.dt,7)='2021-11' 即可

完整代码如下

with t as 
(select *,rank()over(partition by uid order by dt) as rk
from 
(select uid,date(in_time) as dt from tb_user_log
 union 
 select uid,date(out_time) as dt from tb_user_log
) as u )


select t1.dt,round(count(t2.uid)/count(t1.uid),2)
from t as t1 left join t as t2 on t1.uid=t2.uid 
and t1.dt=date_sub(t2.dt,interval 1 day)
where t1.rk=1 and left(t1.dt,7)='2021-11'
group by t1.dt
order by dt

该题有一个易错点，那就是题目中第一句话就说明了在十一月份的，可能做题人会考虑在建立表t时就直接where语句选出11月的，这样是错误的。

原因在于：在建立表t是where会先运行，也就是选择了11月的信息再进行排序。那么如果一个用户10月活跃过，在11月中，11月2号和3号活跃了，这样的操作会使得该用户11-02的排名rk=1，也会被我们认为是新用户，但是显然并非如此。