题解 | 筛选限定昵称成就值活跃日期的用户

很多答案把表中2021年9月当成最近一次活跃日期，没有考虑到万一有10月怎么办。题目问的是最近一次活跃时间为2021年9月。

with 
 #『牛客』开头『号』结尾、成就值在1200~2500之间的作答试卷
t  as   
(select b.*,a.nick_name,a.achievement
from exam_record as b
left join user_info as a
using(uid)
where a.achievement between 1200 and 2500
and a.nick_name like '牛客%号' ),

t1 as #作答试卷筛选的最终结果
(select distinct uid,	nick_name,	achievement 
from t 
where uid in 
(select uid from (
select *,
row_number() OVER (PARTITION BY uid ORDER BY submit_time DESC) as rk  from exam_record ) as tt
where tt.rk=1  and year(submit_time)=2021 and month(submit_time)=9
group by uid)), #筛选出最近一次活跃 作答试卷 在2021年9月的用户信息


 #『牛客』开头『号』结尾、成就值在1200~2500之间的答题试卷
t2  as   
(select c.*,a.nick_name,a.achievement
from practice_record as c
left join user_info as a
using(uid)
where a.achievement between 1200 and 2500
and a.nick_name like '牛客%号' ),



t3 as #答题试卷筛选的最终结果
(select distinct uid,	nick_name,	achievement 
from t2 
where uid in 
(select uid from (
select *,
row_number() OVER (PARTITION BY uid ORDER BY submit_time DESC) as rk  from practice_record ) as tt1
where tt1.rk=1  and year(submit_time)=2021 and month(submit_time)=9
group by uid)) #筛选出最近一次活跃 答题试卷 在2021年9月的用户信息

#合并两个最终结果表并去重
select * from t1
union
select * from t3