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

select uid,count(*) exam_complete_cnt from 
(
select uid,month,submit_time from
(
select uid,month(start_time) month,submit_time,
dense_rank()over(partition by uid order by month(start_time) desc) ranking 
from exam_record
where year(start_time) = '2021'
) a
where ranking <=3
) c
where uid not in (
select uid from 
(
select uid,month,submit_time from
(
select uid,month(start_time) month,submit_time,
dense_rank()over(partition by uid order by month(start_time) desc) ranking 
from exam_record
where year(start_time) = '2021'
) a
where ranking <=3
) b
where submit_time is null
)
group by uid
order by exam_complete_cnt desc,uid desc;