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

解题思路：

1.以『牛客』开头『号』结尾 nick_name like '牛客%' and nick_name like '%号'

2.成就值在1200~2500之间 achievement between 1200 and 2500

3.最近一次活跃（答题或作答试卷）

max(start_time) over(partition by uid)

max(submit_time) over(partition by uid)

步骤：

1.以『牛客』开头『号』结尾

2.成就值在1200~2500之间

select * from user_info
where nick_name like '牛客%' and nick_name like '%号' 
and achievement between 1200 and 2500

3.最近一次活跃（答题或作答试卷）

select 
distinct a.uid
,a.nick_name
,a.achievement
from (
select * from user_info
where nick_name like '牛客%' and nick_name like '%号' 
and achievement between 1200 and 2500)a
left join (
select *,
max(start_time) over(partition by uid) max1
from exam_record )
b 
on a.uid=b.uid
left join (
select *,
max(submit_time) over(partition by uid) max2
from  practice_record) c
on a.uid=c.uid
where max1=b.start_time 
and DATE_FORMAT(b.start_time,'%Y%m')=202109
or
(max2=c.submit_time
and DATE_FORMAT(c.submit_time,'%Y%m')=202109)