Leetcode 1369. 获取最近第二次的活动

https://leetcode-cn.com/problems/get-the-second-most-recent-activity/

with tmp as (select username,activity,startDate,endDate,row_number() over(partition by username  order by startDate desc)rk  from UserActivity)


select a.username,b.activity,b.startDate,b.endDate
from (
select a.username
,case when a.rk>=2 then 2 
      when a.rk=1 then 1 end rk 
from(
select username,max(rk)rk from tmp group by username
)a 
)a left join tmp b on a.username=b.username and a.rk=b.rk 
where b.rk is not null