题解 | #近三个月未完成试卷数为0的用户完成情况#

select uid,count(submit_time) as exam_complete_cnt
from (
    select uid,start_time,submit_time,
    dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) as recent_month 
	# 使用连续排名将每个月分组，按照时间倒序排序，并用顺序数字标记
    from exam_record
) t1
where recent_month<=3
# 获取顺序倒数第3以内的三组
group by uid
having count(start_time) = count(submit_time)
# 开始和结束的数量相同，即当前UID分组，在已知的三个月内试卷都是已完成的。
order by exam_complete_cnt desc,uid desc