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

select
    uid,
    nick_name,
    achievement
from
    test.user_info
where
    uid in (
        select
            uid
        from
            (
                select
                    uid,
                    date_format (time, '%Y%m') 月份,
                    rank() over (
                        partition by
                            uid
                        order by
                            time desc
                    ) 排名
                from
                    (
                        select
                            uid,
                            exam_id tid,
                            start_time time,
                            score
                        from
                            test.exam_record
                        union
                        select
                            uid,
                            question_id tid,
                            practice_record.submit_time time,
                            score
                        from
                            test.practice_record
                    ) a
                where
                    uid in (
                        select
                            uid
                        from
                            test.user_info
                        where
                            nick_name like '牛客%号'
                            and achievement between 1200 and 2500
                    )
            ) b
        where
            排名 = 1
            and 月份 = '202109'
    )