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

# 根据mindate 与 user_id 进行匹配
select round( (select count(*) from 
login l inner join 
(select user_id,min(date) date from login group by user_id) as h
on l.date = DATE_ADD(h.date,INTERVAL 1 DAY) and h.user_id=l.user_id)/(select count(distinct user_id) from login),3)

个人觉得比较直白的方法

1 查询第二天登录的人数

(select count(*) from

login l inner join

(select user_id,min(date) date from login group by user_id) as h

on l.date = DATE_ADD(h.date,INTERVAL 1 DAY) and h.user_id=l.user_id)

2 查询总人数

(select count(distinct user_id) from login)

3 最后根据round 四舍五入到后三位小数