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

select date,ifnull(round(count(next_date)/count(if(last_date is null ,1, null)) ,3 ),0.000) as p
from
(select t1.*,t2.last_date from 
(select l1.user_id,l1.date,l2.date as next_date from login l1 left join  login l2 on l1.user_id =l2.user_id and (l1.date =date_add(l2.date,interval -1 day)))t1  
inner join 
(select l1.user_id,l1.date,l2.date as last_date from login l1 left join  login l2 on l1.user_id =l2.user_id and l1.date>l2.date)t2  
on t1.user_id=t2.user_id and t1.date=t2.date )t3
group by date order by date

坑坑比较多，注意round(0/0) =null
1.t1临时表能够表示每个用户第二天是否有登录（即次日登录，next_date is null表示次日没有登录）
2.t2临时表能够表示每个用户登录当天是否是作为新用户（last_date is null 表示是新用户）
3.两个表内连接，通过每行数据的next_date,last_date是否为null就能确定是否满足计算次日留存率的条件了
4.好烦啊找不到工作！！！