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

select t0.date,ifnull(round(count(distinct t2.user_id)/(count(t1.user_id)),3),0) as `p`
from
(select date from login group by date) as t0
left join
(select user_id,min(date) as date from login group by user_id) as t1
on t0.date = t1.date
left join login as t2
on t1.user_id = t2.user_id and datediff(t2.date,t1.date) = 1
group by t0.date

先找出所有的日期以及每个用户第一次登陆的日期，即新用户的日期，然后再找出第二天还登录的用户

按照次日留存率的公式计算即可，按照日期分组聚合