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

select uid,cntSum as exam_complete_cnt from (
select uid,
sum(case when rankNum >=1 then 1 else 0 end) rankSum,
sum(case when cnt >= 1 then 1 else 0 end) cntSum
from (
select uid,
dense_rank() over(partition  by uid order by DATE_FORMAT(start_time,"%Y%m") desc) as rankNum,
case when score is not null then 1
else 0 
end cnt
from exam_record er)t 
where t.rankNum <= 3
group by uid)t1 where rankSum = cntSum
order by exam_complete_cnt desc ,uid desc

根据uid用dense_rank函数对用户分桶后按年份降序排序，然后过滤出前三名用户。最后再根据排名个数等于答题数捞出符合条件的uid。结果按照答题数用户号降序。