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

首先想法是确定新用户的留存率怎么算，这里其实和之前留存率的想法一样，就是加上了新用户的限制。原来是：N日留存用户数/某日活跃用户数 。现在变成：次日留存新用户数/某日活跃的新用户数

• 那么问题关键是如何确定这个用户是否是新用户，我想到的是，找到每一个用户的最小登录日期，也就是首日登录，作为字段拼接。后续只要判断用户的这一天日期是否和这个字段相等，就可以判断是否是新用户：

  select
                    uid,
                    min(date (in_time)) first_day
                from
                    tb_user_log
                group by
                    uid

• 接下来就是找每天活跃的新用户了，这里之前的想法就是对表进行自联结，通过自联结后的登录日期差，来判断是几日的留存。这里思路也一样，但是注意不同的是连接表有点区别，一个是in_date作为dt，一个是out_date作为dt，因为可能出现跨天登录情况，比如11月1日登录，11月2日登出，这样其实第二天也是活跃。但如果你都用in_date作为dt，就会丢失这种情况的统计:

select
            distinct tb.uid,
            date (in_time) dt,
            first_day
        from
            tb_user_log tb
            join (
                select
                    uid,
                    min(date (in_time)) first_day
                from
                    tb_user_log
                group by
                    uid
            ) t1 on tb.uid = t1.uid
    ) t2
    join (
        select
            distinct tb.uid,
            date (out_time) dt,
            first_day
        from
            tb_user_log tb
            join (
                select
                    uid,
                    min(date (in_time)) first_day
                from
                    tb_user_log
                group by
                    uid
            ) t1 on tb.uid = t1.uid
    ) t3

• 两表结合以后，就按照上面公式进行计算即可，某日活跃的新用户数：count(distinct t2.uid)

次日留存新用户数：(sum( if( datediff(t3.dt,t2.dt)=1 , 1,0 ))

​ 这里加上过滤条件：where t2.dt = t2.first_day

​ 用来过滤新用户。

• 最后不要忘了过滤条件，求的是11月的天数，还有排序。总代码如下：

select
    t2.dt,
    round((sum( if( datediff(t3.dt,t2.dt)=1 , 1,0 )))/count(distinct t2.uid) ,2) uv_left_rate

from
    (
        select
            distinct tb.uid,
            date (in_time) dt,
            first_day
        from
            tb_user_log tb
            join (
                select
                    uid,
                    min(date (in_time)) first_day
                from
                    tb_user_log
                group by
                    uid
            ) t1 on tb.uid = t1.uid

    ) t2
    join (
        select
            distinct tb.uid,
            date (out_time) dt,
            first_day
        from
            tb_user_log tb
            join (
                select
                    uid,
                    min(date (in_time)) first_day
                from
                    tb_user_log
                group by
                    uid
            ) t1 on tb.uid = t1.uid

    ) t3
    on t2.uid=t3.uid
where
    t2.dt = t2.first_day

group by
    dt
having t2.dt between '2021-11-01' and '2021-11-30'
order by
    dt