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

select round(sum(case when a.later_date = date_add(a.date, interval 1 day) then 1 else 0 end) / count(a.user_id), 3) p
from (
        select # 先按用户id分区, 然后将日期列往前一行，添加新的列later_date
        user_id
        ,date
        ,lead(date,1) over(partition by user_id order by date) later_date
        ,row_number() over(partition by user_id order by date) r
        from login
     ) a
where r = 1 # 由于题目要求是新用户，所以筛选出每个用户分区后的第一行，然后再select中判断date列和later_date列是否是加一天的关系