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

第一次提交代码的时候直接用JOIN连接exam_record和practice_record，导致有一条用例没通过，后发现因为该用例中用户1005没有题目作答记录，故没有筛选进连接后的表中，改为left join后成功通过。

SELECT DISTINCT uid, nick_name, achievement
FROM (
    SELECT uid,
        MAX(DATE_FORMAT(start_time, '%Y%m')) OVER(PARTITION BY uid) AS recent_act_exam,
        MAX(DATE_FORMAT(p_r.submit_time, '%Y%m')) OVER(PARTITION BY uid) AS recent_act_practice
    FROM exam_record e_r LEFT JOIN
        practice_record p_r USING(uid)
)recent_act_table
    JOIN user_info USING(uid)
WHERE nick_name LIKE '牛客%号' 
    AND achievement BETWEEN 1200 AND 2500
    AND (recent_act_exam = '202109'
        OR recent_act_practice = '202109')