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

1. 首先筛选出 试卷区和题目区最近一次的活跃月份处于2021年9月的活跃用户。然后从user_Info 表中筛选用户。

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

        select UI.uid,
               UI.nick_name,
               UI.achievement
          from user_info UI 
          where UI.nick_name like '牛客%号' 
            and UI.achievement between 1200 and 2500 
            and UI.uid in (
        select A.uid 
          from (
        select ER.uid,
               ER.start_time exam_time,
               PR.submit_time prac_time,
               row_number() over(partition by ER.uid order by ER.start_time desc) rk1,
               row_number() over(partition by PR.uid order by PR.submit_time desc) rk2 
          from exam_record ER left join practice_record PR using(uid) 
        union 
        select PR.uid,
               ER.start_time exam_time,
               PR.submit_time prac_time,
               row_number() over(partition by ER.uid order by ER.start_time desc) rk1,
               row_number() over(partition by PR.uid order by PR.submit_time desc) rk2 
          from exam_record ER right join practice_record PR using(uid) 
          ) A where (rk1=1 and date_format(A.exam_time,'%Y%m')='202109') or 
             (rk2=1 and date_format(A.prac_time,'%Y%m')='202109')
             
             )