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

select uid,nick_name,achievement
from user_info
where nick_name like "牛客%号" and achievement between 1200 and 2500 and uid in (
select uid
from
(with a as (select uid,date_format(start_time,"%Y%m") date1
from exam_record
union all
select uid,date_format(submit_time,"%Y%m") date1
from practice_record) /*上下连接两表,查找最近活跃时间*/
select *,dense_rank() over(partition by uid order by date1 desc) rankin
from a) a1 /* 开窗找最近时间*/
where rankin=1 and date1="202109")