LeetCode 【困难】数据库-第1369 ：获取最近第二次的活动

题目

数据

结果

解答

1

select *,
rank() over(partition by username order by startdate desc) as rn,
count(*) over(partition by username) cnt
from useractivity

2

select * 
from 
(select *,
rank() over(partition by username order by startdate desc) as rn,
count(*) over(partition by username) cnt
from useractivity)b
where rn = 2 or cnt=1;