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

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

https://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450

（我的思路有点偏，你可以参考评论中其他人写的、比较容易明白的那种可能更好）

这个题我干了50分钟，最麻烦的就是后面的改错，只要把思路捋清楚，这个题还是容易。后面改错花了我大量时间。

比较重要的点：1、什么是新用户：第一次在这张表出现的用户访问记录的用户，就是新用户

2、跨天的情况，这里作者比较仁慈，只会跨一天，可以思考思考跨多天的情况，但这里只需要考虑跨一天--》当然采用union连接起来。

3、对于新用户，如何获取该用户的下一次访问日期（下一次的年月日访问和当前不同的日期）。当然是开窗。判断紧按着我想日期加减应该不是问题。

下面是我的思路（###是我遇到的问题）

######主要问题在于没有写错了字段，我把union 两个 in_time 和out_time都写成一样的，就死活通不过，检查了至少8分钟，服了。

######第二问题就是没有注意到统计的范围，以及哪里取过滤掉不符合范围的数据，我原来把过滤2021-11记录的where写在了union的两个句子中，出现了问题，问题说明：因为数据库存在以前的用户访问数据，如果先把范围数据过滤出来，那么可能出现后面的rk就会误认为当前第一次该用户访问属于新用户的访问，但其实这个用户在 2021-11前已经存在了访问记录，这个用户就不能算是新用户。所以在最外面一层用having 过滤出了范围数据，不会影响rk对新用户的判定。

从里到外

step1: 因为可能存在夸一天的访问，所以需要吧一条访问记录拆成两天，分别以in_time,和out_time为活动日期，将他们union.

（这里不能吧2021-11的数据给筛选出来，）

step2：用row_number() 函数通过uid进行分区进行开窗，得到该用户的出现次序，记录为rk，rk=1的就是第一次出现的记录，是新用户。（这里就是为什么step1不能用where过滤出数据的原因）

step3: 这一部主要是用case when 判断当前记录是不是新用户，并且判断条件中的一个条件采用了通过uid用户开窗，按照日期升序排序，得到这个用户的下一次出现的日期，如果当前记录日期+1后和用户下一次出现的日期相等且是新用户（rk=1），那么将nextDayKeep 记录为1,

step4:最后按照日期分组，分组条件中过滤出2021-11的数据，（当然你可以在step3中过滤也行），新用户的下一天留存数就是sum(nextDayKeep) ，而新用户就是rk=1的记录的个数。排序即可

select
    e.activityTime as dt,
    format(sum(nextDayKeep)/sum(case when e.rk = 1 then 1 else 0 end),2) as uv_left_rate
from 
(
    select
        d.uid, 
        d.rk,
        d.activityTime,
        case when d.rk = 1 and date_add(d.activityTime,interval 1 day) = lead(d.activityTime,1) over(partition by d.uid order by d.activityTime) then 1 else 0 end as nextDayKeep
    from 
    (
        select
            c.uid,c.activityTime,row_number() over(partition by c.uid) as rk
        from 
        (
            select
                a.uid,date_format(a.in_time,'%Y-%m-%d') as activityTime
            from tb_user_log a 

            union 

            select
                b.uid,date_format(b.out_time,'%Y-%m-%d') as activityTime
            from tb_user_log b
        ) c
    ) d
) e group by e.activityTime having sum(case when e.rk = 1 then 1 else 0 end) > 0 and e.activityTime>='2021-11-01' and e.activityTime <= '2021-11-30'
order by e.activityTime