题解 | #近三个月未完成试卷数为0的用户完成情况#
近三个月未完成试卷数为0的用户完成情况
https://www.nowcoder.com/practice/4a3acb02b34a4ecf9045cefbc05453fa
select uid, count(score) as exam_complete_cnt from( select uid, start_time,submit_time, score, dense_rank() over(partition by uid order by date_format(start_time, '%Y%m') desc) as recent_months from exam_record ) recent_table where recent_months <= 3 group by uid having count(score) = count(uid) order by exam_complete_cnt desc, uid desc
本题主要考核的是dense_rank()over(partition by uid date_format(start_time, '%Y%m') desc),近三个月的意思是最靠近现在的三个月可以用desc来表示,having count(score) = count(uid)来表示分数的量和uid的数量一样多,也就是没有null的情况。