题解 | #近三个月未完成试卷数为0的用户完成情况#
近三个月未完成试卷数为0的用户完成情况
http://www.nowcoder.com/practice/4a3acb02b34a4ecf9045cefbc05453fa
with t as
(SELECT uid,start_time,submit_time,DENSE_RANK()over(partition by uid order by DATE_FORMAT(start_time,"%y%m") desc) as rn
from exam_record)
select uid,count(1) as exam_complete_cnt
from t
where rn <=3 and uid not in (select uid from t where submit_time is null and rn<=3)
group by uid
order by exam_complete_cnt desc,uid desc
