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

select t2.uid,count(t2.month_submit) as exam_complete_cnt 
from
(SELECT t1.uid,t1.month_submit from(
select  uid,date_format(start_time,'%Y-%m') as month_start,
dense_rank() over(partition by uid order by date_format(start_time,'%Y-%m') desc) as rank_date,
DATE_FORMAT(submit_time,'%Y-%m') as month_submit from 
exam_record ) t1 
where rank_date <= 3 ) t2 
group by t2.uid having count(*)=count(t2.month_submit) 
order by exam_complete_cnt desc,t2.uid desc