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

• 计算sql试卷未完成率，并用ROW_NUMBER()OVER()对其进行排名

SELECT uid,
    ROW_NUMBER()OVER(ORDER BY incomplete_rate DESC, uid DESC) AS incomplete_rank
FROM (
    SELECT uid,
        incomplete_cnt / total_cnt AS incomplete_rate
    FROM (
        SELECT uid, 
            COUNT(CASE WHEN tag = 'SQL' AND submit_time IS NULL
                THEN exam_id END) AS incomplete_cnt,
            COUNT(CASE WHEN tag = 'SQL' THEN exam_id END) AS total_cnt
        FROM exam_record JOIN 
            examination_info USING(exam_id)
        GROUP BY uid
        )cnt_table 
    )rate_table 
)rank_table

• 计算作答试卷的总人数

SELECT COUNT(DISTINCT uid) AS total_user
FROM exam_record

• 计算sql试卷未完成率较高的50%的用户：incomplete_rank <= ceiling(total_user / 2)

• 完整代码如下：

SELECT uid, start_month,
    COUNT(start_time) AS total_cnt,
    COUNT(submit_time) AS complete_cnt
FROM (
    SELECT uid, exam_id, start_time, submit_time, total_user,
        DATE_FORMAT(start_time, '%Y%m') AS start_month,
        DENSE_RANK()OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time, '%Y%m') DESC) AS recent_months_rank
    FROM exam_record JOIN (
        SELECT uid,
            ROW_NUMBER()OVER(ORDER BY incomplete_rate DESC, uid DESC) AS incomplete_rank
        FROM (
            SELECT uid,
                incomplete_cnt / total_cnt AS incomplete_rate
            FROM (
                SELECT uid, 
                    COUNT(CASE WHEN tag = 'SQL' AND submit_time IS NULL
                         THEN exam_id END) AS incomplete_cnt,
                    COUNT(CASE WHEN tag = 'SQL' THEN exam_id END) AS total_cnt
                FROM exam_record JOIN 
                    examination_info USING(exam_id)
                GROUP BY uid
            )cnt_table 
        )rate_table 
    )rank_table USING(uid) JOIN (
            SELECT COUNT(DISTINCT uid) AS total_user
            FROM exam_record
        )user_table
    WHERE incomplete_rank <= ceiling(total_user / 2)
        AND uid IN (
            SELECT uid
            FROM user_info
            WHERE level BETWEEN 6 AND 7
        )
)recent_month_table
WHERE recent_months_rank <= 3
GROUP BY uid, start_month
ORDER BY uid, start_month