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

# 先筛选出昵称以『牛客』开头『号』结尾、成就值在1200~2500之间的用户信息
with t1 as(
select uid, nick_name, achievement
from user_info
where nick_name like '牛客%' and nick_name like '%号'
and achievement between 1200 and 2500), t2 as(
# 查出各用户的答题或作答试卷记录表
select uid, date_format(start_time, '%Y%m') huoyue_month
from exam_record
union all
select uid, date_format(submit_time, '%Y%m')
from practice_record)

# 从以上用户中找出最近一次活跃（答题或作答试卷）在2021年9月的用户
select t1.uid, nick_name, achievement
from t1 join t2 on t1.uid = t2.uid
group by t1.uid
having max(huoyue_month) = '202109';