select 
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date)
in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id);

1、找到每个用户第一天登录的日期和次日也登录的记录

where (user_id,date) in (
  select user_id,
  	DATE_ADD(min(date),INTERVAL 1 DAY) 
  from login 
  group by user_id);

2、所有用户的个数

select count(distinct user_id) 
from login

3、记录次留存率

round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) 
	  ,3)

4、完整代码如下

select round(count(distinct user_id)/(
    select count(distinct user_id) 
    from login) 
    ,3)
from login
where (user_id,date) in (
    select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) 
    from login 
    group by user_id);