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

使用窗口函数

select round(sum(date)/count(*),3) p from
(select distinct user_id,
case when
(nth_value(date,2) over w - first_value(date) over w)=1 then 1 else 0 end
as date
from login l
window w as(
partition by user_id order by date asc rows between unbounded preceding and unbounded following
))a