题解 | #牛客每个人最近的登录日期(三)#
牛客每个人最近的登录日期(三)
http://www.nowcoder.com/practice/16d41af206cd4066a06a3a0aa585ad3d
使用窗口函数
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
查看2道真题和解析