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

select
t.uid,
sum(case when t.pday is not null then 1 else 0 end) as 	exam_complete_cnt
from(
select
uid,
date(start_time) as sday,
date(submit_time) as pday,
dense_rank()over(partition by uid order by date_format(start_time,"%Y%m") desc) as m
from exam_record 
) as t 
where
t.m<=3
group by t.uid
having sum(case when t.pday is not null then 0 else 1 end)=0
order by exam_complete_cnt desc,uid desc

题意为：3个月内没有未完成的状态的UID的完成次数。