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

# 昵称以『牛客』开头『号』结尾、成就值在1200~2500之间，且"最近"一次活跃（答题或作答试卷）在2021年9月的用户信息
# "最近"一次活跃时间
with A as (
select uid,max(m)m
from (
select uid,max(date_format(start_time,'%Y%m'))m from exam_record group by 1
union
select uid,max(date_format(submit_time,'%Y%m'))m from practice_record group by 1
)a
group by 1
having m = 202109
)
# 试卷作答记录表
select distinct uid,nick_name,achievement
from user_info
join A using(uid)
left join exam_record e using(uid)
left join practice_record p using(uid)
where (date_format(start_time,'%Y%m') = 202109
or date_format(p.submit_time,'%Y%m') = 202109)
and (achievement between 1200 and 2500)
and nick_name like '牛%'
and nick_name like '%号'