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

select uid,nick_name,achievement from ( select e_r.uid as uid,nick_name,achievement ,max(date_format(e_r.start_time,'%Y%m')) over (partition by e_r.uid order by date_format(e_r.start_time,'%Y%m')) as cecent_time ,max(date_format(p_r.submit_time,'%Y%m')) over (partition by p_r.uid order by date_format(p_r.submit_time,'%Y%m')) as recent_time from exam_record e_r left join user_info u_i on u_i.uid=e_r.uid left join practice_record p_r on p_r.uid=e_r.uid

where nick_name like '牛客%号' and (achievement between 1200 and 2500) ) t where recent_time like '202109' or cecent_time like '202109' group by uid order by uid