题解 | #未完成率较高的50%用户近三个月答卷情况#

该题重点在“SQL试卷上未完成率较高的50%的用户”，将此要求作为用户的子查询，对于此句的理解，举个例子：假设作答过试卷的总用户数为60，其中作答过sql试卷的有50人，取总人数的一半即为30人（若为小数则向上加一位），按sql试卷未完成率对用户进行降序排序，排名小于等于30的即为未完成率较高的人。

SELECT uid,
       DATE_FORMAT(start_time, '%Y%m') AS start_month,
       COUNT(start_time) AS total_cnt,
       COUNT(score) AS complete_cnt
FROM (
    SELECT uid, start_time, score,
           DENSE_RANK()OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time, '%Y%m') DESC) AS recent_months
    FROM exam_record
) months_table
WHERE recent_months <= 3 
    AND uid IN (
        SELECT incomplete_table.uid
        FROM (
            SELECT uid,
            ROW_NUMBER()OVER(ORDER BY incomplete_rate DESC, uid DESC) AS incomplete_rank
            FROM (
                SELECT uid,
                    SUM(IF(score IS NULL, 1, 0)) / COUNT(start_time) AS incomplete_rate
                FROM exam_record JOIN examination_info USING(exam_id)
                WHERE tag = 'SQL'
                GROUP BY uid
            )incomplete_rate_table 
        )incomplete_table JOIN (
            SELECT COUNT(DISTINCT uid) AS user_total
            FROM exam_record
        )user_total_table
        JOIN user_info USING(uid)
        WHERE level BETWEEN 6 AND 7
            AND incomplete_rank <= ceiling(user_total / 2)
    )
GROUP BY uid, start_month
ORDER BY uid ASC, start_month ASC