题解 | #近三个月未完成试卷数为0的用户完成情况#
近三个月未完成试卷数为0的用户完成情况
https://www.nowcoder.com/practice/4a3acb02b34a4ecf9045cefbc05453fa
with data_f_use as ( select uid, exam_id, start_time, submit_time, month_rank from ( select uid, exam_id, start_time, submit_time, dense_rank()over(partition by uid order by substr(start_time, 1, 7) desc) month_rank from exam_record) a ) select uid, count(exam_id) exam_complete_cnt from data_f_use where month_rank <= 3 group by uid having count(start_time) = count(submit_time) order by exam_complete_cnt desc, uid desc
上面的代码通过了，但是！！！
如果有人只有一个月或两个月的作题数据符合条件，也就是说month_rank没有3， 那是不是应该将这些人剔除？
所以我在having后面加了一个筛选条件 and max(month_rank) >= 3 （完整代码见下图），但是却没有得到结果，并提示未通过用例，真是不理解
with data_f_use as ( select uid, exam_id, start_time, submit_time, month_rank from ( select uid, exam_id, start_time, submit_time, dense_rank()over(partition by uid order by substr(start_time, 1, 7) desc) month_rank from exam_record) a ) select uid, count(exam_id) exam_complete_cnt from data_f_use group by uid having count(start_time) = count(submit_time) and max(month_rank) >= 3 order by exam_complete_cnt desc, uid desc
