#2、其次，按照结果数据表的展示情况，筛选后展示所需字段#
select t1.uid,count(t1.start_time) as exam_complete_cnt
from

#1、首先，针对第1个需求“每位用户近三个月份有试卷作答记录”：#
#step1、按照月份进行倒序(找近期月份的xx名👉倒序排序)编号：①1个用户可能在1个月内存在多个试卷答题数👉dense_rank满足该需求；②求近期的月份👉对月份进行倒序排序的desc#
#step2、将编号前3位筛选出来#
#分2步走的原因：窗口函数与where存在矛盾，因此针对“排名xx”的需求往往就拆分成两步#
(select uid,
date_format(start_time,'%y%m') as start_time,
date_format(submit_time,'%y%m') as submit_time,
#1.1、为了满足1个用户在1个月内答题数量存在>1的情况，那么适用dense_rank(若出现相同字段，则实行相同编号，且其不算多一个位置)就能满足该需求#
dense_rank() over(partition by uid order by date_format(start_time,'%y%m') desc) as rank1
from exam_record) as t1

#2.1、“近3个月” = 按照start_time倒序排序的编号前3位#
where t1.rank1 <= 3
group by 1

#3、⭐为了满足“若用户近3个月存在未答完的情况，则不计算其答题数”，进而按照每位用户start_time的数量=其submit_time的数量，即用户做题要与提交同时存在，否则就算未提交，就不计算该用户答题数#
having count(t1.start_time)=count(t1.submit_time)
order by 2 desc,1 desc;