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

# 先找到昵称符合条件的用户uid
with t1 as (
    select uid, nick_name, achievement
    from user_info
    where nick_name REGEXP '^牛客[0-9]*号$'
    and achievement between 1200 and 2500
),
# 与试卷表进行左连接，查找最近一次的试卷记录
t2 as (
    select t1.uid, t1.nick_name, t1.achievement, max(b.start_time) as recent_time
    from t1
    left join exam_record as b
    on t1.uid  = b.uid
    group by t1.uid
    having max(b.start_time) between '2021-09-01' and '2021-09-30'
),
# 与练习表进行左连接，查找最近一次的试卷记录
t3 as (
    select t1.uid, t1.nick_name, t1.achievement, max(c.submit_time) as recent_time
    from t1
    left join practice_record as c
    on t1.uid = c.uid
    group by t1.uid
)
# 将为空的舍去，然后进行去重连接union
select uid, nick_name, achievement
from t2
where recent_time IS NOT NULL
union 
select uid, nick_name, achievement
from t3
where recent_time IS NOT NULL;