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

select date,count(case when (user_id,date) in 
(select user_id,min(date)  
from login
group by user_id)
then 1 else null end) ne
from login
group by date 

结果如下：

再写出新用户次日登录人数表：

select date,count(case when (user_id,date) in
(select user_id,date_add(min(date),interval 1 day) 
from login
group by user_id)
then 1 else null end) re
from login
group by date 

结果如下：

两个表相连接后得到以下结果：

根据题目要求我们可以知道分母就是12号当天的新用户数，分子就是13号用户次日登录的个数，如果直接两列相除是不对的，需要往下偏移一行，用13号次日新用户登录人数/12号当天的新用户数，即2/3=0.667；

lead(a1.re,1)over(order by a1.date)，结果如下

最后用偏移的列除以第一列即可，注意这里要用ifnull函数，完整代码如下

select a1.date date,ifnull(round(lead(a1.re,1)over(order by a1.date)/a2.ne,3),0) p
from
(
select date,count(case when (user_id,date) in
(select user_id,date_add(min(date),interval 1 day) 
from login
group by user_id)
then 1 else null end) re
from login
group by date ) a1      /*用户第二天也登录的人数*/
join 
(
select date,count(case when (user_id,date) in 
(select user_id,min(date)  
from login
group by user_id)
then 1 else null end) ne
from login
group by date     /*每天新用户的人数*/
) a2
on 
a1.date=a2.date