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

select A.fir_time as dt, round(count(J.sec_time)/count(A.fir_time),2) as uv_left_rate from
(select uid, min(date(in_time)) as fir_time from tb_user_log group by uid) as A #每日新增用户
left join
(select * from
(select uid, sec_time, if(@pre = uid, @rownum:=@rownum+1,@rownum:=1) as rownum, @pre:= uid from
(select * from
	((select uid, date(in_time)as sec_time from tb_user_log)
	union
	(select uid, date(out_time)as sec_time from tb_user_log))as C
    group by uid, sec_time
    order by uid, sec_time) as G,
(select @pre:='', @rownum:=0) as H) as I
where rownum = 2) as J  #第二次活跃用户
on A.uid = J.uid
group by dt
having dt>= "2021-11-01" and dt< "2021-12-01"
order by dt;

Step 1 求出每日新增用户表

Step 2 求出第二次活跃用户及其第二次活跃时间的数据表

Step 3 将两表join起来

Step 4 通过（第二次活跃用户数/新增用户数）计算留存率

有两个问题尚未解决，希望有大佬能给我一些思路：

1）使用添加行号的方法得出每个用户第二次活跃的时间有些繁琐，是否有更简便的方法

2）此代码没有筛选用户第二次活跃和第一次注册之间相差时候是否是1天，不知道该如何在代码上写出来