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

#每个用户首次活跃的日期,表1
select uid,date(min(in_time))
from tb_user_log
group by uid

#每个用户全部活跃的日期，表2
select uid,date(in_time)as dt
from tb_user_log
union
select uid,date(out_time)as dt
from tb_user_log
order by uid

#查看当日、次日用户的活跃情况，次日uid为空说明次日没有留存
#返回四列：当日日期，uid，次日日期，uid
#表1左连接表2，条件是日期相差1天
1、timestampdiff(单位，开始时间，结束时间)
单位：sencond,minute,hour,day,week,month,year
2、from...join...on+条件
select *
from
    (select uid,date(min(in_time))as dt
    from tb_user_log
    group by uid)as a
left join
    (select uid,date(in_time)as dt
    from tb_user_log
    union
    select uid,date(out_time)as dt
    from tb_user_log
    order by uid)as b
on a.uid=b.uid
and timestampdiff(day,a.dt,b.dt)=1

#计算次留
1、round(a,3):a保留三位小数
select a.dt
    ,round(count(b.uid)/count(a.uid),2)as uv_left_rate
from
    (select uid,date(min(in_time))as dt
    from tb_user_log
    group by uid)as a
left join
    (select uid,date(in_time)as dt
    from tb_user_log
    union
    select uid,date(out_time)as dt
    from tb_user_log
    order by uid)as b
on a.uid=b.uid
and timestampdiff(day,a.dt,b.dt)=1
where a.dt like"2021-11%" #限制时间范围
group by a.dt