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

select
    t1.dt,ifnull(round((count(t2.uid)/count(t1.uid)),2),0)  as uv_left_rate
from
    (
        select
            uid,
            min(date (in_time)) dt
        from
            tb_user_log
        group by
            uid
    )  as t1
    left join (
        select
            uid,
            date (in_time) dt
        from
            tb_user_log
        union
        select
            uid,
            date (out_time) dt
        from
            tb_user_log
    ) as t2
    on t2.dt =DATE_ADD(t1.dt, INTERVAL 1 DAY)
    and t1.uid = t2.uid
    where date_format(t1.dt,"%Y%m")=202111
    group by dt
    order by dt

先建立一个表存储新用户的日期，min()得出用户最小天数，用户开始时间日期和结束时间日记合并得到用户活跃日期表，

左链接两个表，用户名相同，日期加一相等，得出计算的表，左边用户就是当天的用户人数，右边代表第二天活跃若为空值则代表次日不活跃，group by count 计算次日留存率