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

with s1 as (select *
,rank()over(partition by uid order by month desc) ranking 
from(
select ui.uid,nick_name,date_format(start_time,'%Y%m') month,achievement,level
from user_info ui left join exam_record er 
on ui.uid = er.uid
where nick_name like '牛客%'
union all
select ui.uid,nick_name,date_format(submit_time,'%Y%m') month,achievement,level
from user_info ui left join practice_record pr
on ui.uid = pr.uid
where nick_name like '牛客%') a1)
select distinct uid,nick_name,achievement from s1
where ranking = 1 and month like '202109'
and achievement between 1200 and 2500;