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

select uid,sum(ans2) as exam_complete_cnt
from
(select uid,date_format(start_time,"%Y%m") as u_month,
dense_rank() over(partition by uid order by date_format(start_time,"%Y%m") desc) as u_rank,
count(start_time) as ans1,
count(submit_time) as ans2
from exam_record
group by uid,u_month) tb1
where u_rank <= 3 
group by uid
having sum(ans1) = sum(ans2)
order by exam_complete_cnt desc,uid desc