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

select uid,
count(score) as exam_complete_cnt
from (
select *,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc) as rk from exam_record) t
where rk <= 3 
group by uid
having count(score) = count(start_time)

order by exam_complete_cnt desc,uid desc

dense_rank的应用，先把最近三个月的所有信息都找出来，然后降序排列选出最近三个月的记录，按照uid分组并筛选出作答记录完整的结果输出