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

# 第四次 根据首次登陆时间 即最开始的first_time聚合，通过sum计算第二天活跃人数，通过count计算新用户总数（注意：由于第三次子查询已经对uid进行聚合了，所以此次不需要再使用distinct去重）
select 
	c.dt,
	round(sum(c.cnt)/count(c.uid),2) as rate
from # 第三次子查询，通过用户uid聚合，判断该用户是否为第二天活跃用户，若是则赋值为1（此步为去除新用户第二天多次登录的重复记录）
  (select 
	  b.uid,date_format(b.first_time,'%Y-%m-%d') as dt,
	  if(sum(cnt)>=1,1,0) as cnt
  from # 第二次子查询，通过判断登陆时间和首次登陆时间的差是否为1，或者登录登出时间差是否大于等于1确定该用户是否为次日登录的活跃用户（注意：这时可能存在新用户第二天多次登录的情况）
	(select 
		a.*,
		if(datediff(a.in_time,a.first_time)=1 or (datediff(a.out_time,a.in_time)=1 and a.in_time=a.first_time),1,0) as cnt
	from # 第一次子查询，使用开窗函数确定每个用户的首次登陆时间，记为first_time
		(select
			 tu.*,
			 min(in_time) over(partition by tu.uid) as first_time
		 from tb_user_log tu) as a) as b
  group by b.uid,b.first_time) as c
group by c.dt
having date_format(c.dt,'%Y-%m')='2021-11' 
;

四次子查询，可以在本地从第一层开始运行，通过实例理解