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

SELECT n.uid, COUNT(*) AS exam_complete_cnt
FROM(
    SELECT *, 
    DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE_FORMAT(submit_time, '%Y%m') DESC)
    AS ra
    FROM exam_record) AS n # 筛选近三个月有答题记录的信息
WHERE n.ra <= 3 AND n.uid NOT IN (SELECT z.uid
                                  FROM(SELECT *, 
                                       DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time, '%Y%m') DESC) AS ra # 注意这里为starttime才能筛选掉有未完成试卷的用户（又开始时间无提交时间）
                                    FROM exam_record) AS z # 筛选近三个月有答题记录信息
                                WHERE z.ra <= 3
                                AND z.score IS NULL)  # 过滤有未完成试卷的用户

GROUP BY n.uid
ORDER BY exam_complete_cnt DESC, n.uid DESC;