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

# 注意点：1 统计时间：2021年11月
#        2 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过
#        3 结果按日期升序

# 由于跨日计算为两天均活跃，所以将in_time 和 out_time 通过 union 函数合并，并通过 distinct去重，此时不要过滤登录时间。代码如下：
with
    t1 as (
        select distinct
            uid,
            date (in_time) sin_date
        from
            tb_user_log
        union
        select distinct
            uid,
            date (out_time) sin_date
        from
            tb_user_log
    ),
# 统计时间：2021年11月，需要将不是11月的新用户通过 where 过滤掉，注意要过滤用户最小的登录时间，而不是仅过滤用户的登录时间，否则10月的老用户在11月份登录时，容易被误记为新用户。 
# 过滤后的uid就是当日的新用户数量。
    t2 as (
        select
            min(sin_date) dt,
            uid total_id
        from
            t1
        group by
            uid
        having
            date_format (min(sin_date), '%Y-%m') = '2021-11'
    )
# 计算 次日留存的用户数量，通过 datediff 计算出最早登录时间和下次登录时间差值为1的用户，按照最早登录时间分组并计算新用户和留存用户的数量。按照日期升序排序。
select
    dt,
    round(count(uid) / count(total_id), 2) uv_left_rate
from
    t2
    left join t1 on t1.uid = t2.total_id
    and datediff (sin_date, dt) = 1
group by
    dt
order by
    dt;