题解 | #牛客每个人最近的登录日期(三)#

1.获得总用户数
select count(distinct user_id) from login

2.获得每位用户第二次登录的理论时间next_date，t1表
select user_id, DATE_ADD(min(date),INTERVAL 1 DAY) next_date
from login
group by user_id;t1

3.用user_id连接原表和t1表，计数原表中date中存在next_date时的user_id的个数
select count(distinct t1.user_id)
from login l1
join (select user_id, DATE_ADD(min(date),INTERVAL 1 DAY) next_date
from login
group by user_id )t1
on l1.user_id=t1.user_id
where l1.date=t1.next_date

4.将第3步的值除以第1步的值，结果保留三位小数
select round(count(distinct t1.user_id)/(select count(distinct user_id) from login),3)
from login l1
join (select user_id, DATE_ADD(min(date),INTERVAL 1 DAY) next_date
from login
group by user_id )t1
on l1.user_id=t1.user_id
where l1.date=t1.next_date