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

select
*
from
(
    select
    uid
    ,case when substring(start_time,1,7) = '2021-09' or substring(submit_time,1,7) = '2021-09' then nick_name else null end nick_name
    ,achievement
    from
    (
        #以用户信息表作为主表，试卷作答记录表和练习作答记录表连接，并且筛选出试卷作答和练习作答的最近一次时间
        select
        ui.uid uid
        ,nick_name
        ,start_time
        ,pr.submit_time submit_time
        ,achievement
        ,row_number()over(partition by uid order by er.start_time desc) ranker
        ,row_number()over(partition by uid order by pr.submit_time desc) rankpr
        from  user_info ui left join exam_record er
        on ui.uid = er.uid
        left join practice_record pr
        on ui.uid = pr.uid
    )table1
    where ranker = 1
    and rankpr = 1
    and achievement >= 1200
    and achievement <= 2500
    and nick_name like '牛客%号'
)table2
where nick_name is not null