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

select f_day,round(sum(if(a>0,1,0))/count(distinct uid),2) from(
select u.uid,f_day,
sum(if(date_format(out_time,'%Y-%m-%d')=s_day or date_format(in_time,'%Y-%m-%d')=s_day,1,0)) a 
from tb_user_log t
left join (
    select uid,date_format(min(in_time),'%Y-%m-%d') f_day,
    date_add(date_format(min(in_time),'%Y-%m-%d'),interval 1 day) s_day
    from tb_user_log 
    group by uid
) u  on u.uid=t.uid
where date_format(f_day,'%Y-%m')='2021-11'
group by f_day,u.uid
) l
group by f_day
order by f_day
#（1）先将每个user登陆的第一天和第二天提取出来作为新表LEFT JOIN原表当中，这个时候可以判断第一天是不是在2021-11。
#（2）之后按照user进行GROUP BY，如果user的in_time或oue_time中包含第二天，那么就可以认为这个新用户是第一天登录并且第二天也活跃了，生成一个新字段，用1表示上述用户，0表示不符合条件的用户。
#（3）之后按照第一天进行GROUP BY，对于上述新字段求和就可以得到每天符合条件的user，除以count(user)或者count（新字段）得到留存比率。