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

用了2次窗口函数，我用的计算可能比较长
首先计算第二次登录日期，首次登录的次日，计算登录日期的顺序判断哪天时首次登录
select user_id,date,lead(date,1,date) over(partition by user_id order by date) as date1,
date_add(date,INTERVAL 1 DAY) as date2,
row_number() over(partition by user_id order by date) as date_m
from login
然后在此查询结果中计算每个日期新用户的次日留存率
sum(case date1-date2 when 0 then 1 else 0 end) #计算每日新用户次日留存数
sum(case date_m when 1 then 1 else 0 end) #计算每日新用户数
由于sum(case date_m when 1 then 1 else 0 end)会存在0，每日新用户次日留存数/每日新用户数会存在NULL，所以作了一次IF判断。

代码如下：
select a.date,
ROUND(IF(sum(case date_m when 1 then 1 else 0 end)=0,0,
sum(case date1-date2 when 0 then 1 else 0 end)/sum(case date_m when 1 then 1 else 0 end)),3) p
from
(select user_id,date,lead(date,1,date) over(partition by user_id order by date) as date1,
date_add(date,INTERVAL 1 DAY) as date2,
row_number() over(partition by user_id order by date) as date_m
from login) a
group by a.date