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

1、题目要求我们统计新用户的留存率，翻译成人话就是，计算用户的第一次登录和第二次登录（跨天）是否相隔一天，不在乎后续的登录操作，因此，我们直接row_number分别取rn为1和2的即可，然后用rn为1的作为主表，关联rn为2的，datediff函数判断结果是否为1即可

2、而何时登录，数据里并未直接告诉我们，但是数据里有intime和outtime，我们只需对这两个时间进行一个简单的列转行即可，但要注意应该用union而非union all，因为如果同一天登入又登出，或者同一天多次登入或登出，我们应将其看做一次登录，而上一题用union all是因为我们求解的维度是文章，而非用户

3、由于我们要对t表数据进行两次应用，因此我们将t表使用with as 作为一个临时表提前查出其结果，之后left join的时候，可能存在一个情况，那就是用户就今天登录一次，之后就再无登录过了，但由于这部分数据我们也要作为分母参与平均，因此不将其过滤，而是如果链接不上为null，就给其一个最大的日期即可，反正相减结果不为1

4、题目里限制要求，只要11月份的新用户数据，因此我们对t1进行相应的月份过滤即可，这个过滤千万不要写在union的两个子句中，因为写在那里，会直接将10月的新用户，又在11月登录的数据，看成第一次登录，这显然是不合理的，我们直接在最外层过滤即可，反正我将全部数据都给你，最后你只要11月的，我给你过滤就好了

with t as (
    select
    uid,
    dt,
    row_number() over(partition by uid order by dt) as rn
    from
    (
        select
        uid,
        date(in_time) as dt
        from tb_user_log
        union
        select
        uid,
        date(out_time) as dt
        from tb_user_log
    )a
)
select
t1.dt as dt,
round(
    avg(if(datediff(ifnull(t2.dt,'9999-12-31'),t1.dt) = 1, 1, 0)),2
) as uv_left_rate
from t as t1
left join t as t2
on t1.uid = t2.uid and t2.rn = 2
where t1.rn = 1 and date_format(t1.dt,'%Y-%m') = '2021-11'
group by dt
order by dt