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

SELECT
uid,
COUNT(submit_time) AS exam_complete_cnt
FROM
(SELECT 
uid,
submit_time,
DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time,'%Y%m') DESC) AS rk
FROM exam_record) t1
WHERE rk <=3
GROUP BY uid
HAVING COUNT(IF(submit_time IS NULL,1,NULL))=0
ORDER BY exam_complete_cnt DESC,uid DESC

SELECT
uid,
COUNT(submit_time) AS exam_complete_cnt
FROM
(SELECT 
uid,
submit_time,
DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time,'%Y%m') DESC) AS rk
FROM exam_record) t1
WHERE rk <=3
GROUP BY uid
HAVING COUNT(submit_time) = COUNT(uid)
ORDER BY exam_complete_cnt DESC,uid DESC