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

一、新的知识点

二、思路

1.将SQL试卷上未完成率作为新表t1方便后续查询

SELECT uid, (COUNT(score) / COUNT(uid)) rate
        , PERCENT_RANK() OVER(ORDER BY (COUNT(score) / COUNT(uid))) rate_rank
    FROM exam_record er
    JOIN examination_info ei
    ON er.exam_id = ei.exam_id
    WHERE tag = 'SQL'
    GROUP BY uid

2.找到目标的uid，即SQL试卷上未完成率较高的50%用户中的6级和7级用户

SELECT t1.uid
FROM (SELECT uid, (COUNT(score) / COUNT(uid)) rate
        , PERCENT_RANK() OVER(ORDER BY (COUNT(score) / COUNT(uid))) rate_rank
    FROM exam_record er
    JOIN examination_info ei
    ON er.exam_id = ei.exam_id
    WHERE tag = 'SQL'
    GROUP BY uid) t1
JOIN user_info ui
ON ui.uid = t1.uid
WHERE rate_rank <= 0.5
AND `level` in (6,7)

3.找出解题需要的几个字段uid、start_month、time_rank、submit_time，作为一个新表t2

WITH t2 AS 
(SELECT uid, date_format(start_time, '%Y%m') start_month
    , DENSE_RANK() OVER(partition by uid order by date_format(start_time, '%Y%m') DESC) time_rank
    , date_format(submit_time, '%Y%m') submit_time
FROM exam_record
WHERE uid in (
SELECT t1.uid
FROM (SELECT uid, (COUNT(score) / COUNT(uid)) rate
        , PERCENT_RANK() OVER(ORDER BY (COUNT(score) / COUNT(uid))) rate_rank
    FROM exam_record er
    JOIN examination_info ei
    ON er.exam_id = ei.exam_id
    WHERE tag = 'SQL'
    GROUP BY uid) t1
JOIN user_info ui
ON ui.uid = t1.uid
WHERE rate_rank <= 0.5
AND `level` in (6,7)
))

4.最后就是直接在表t2上进行查询就可以啦

SELECT uid, start_month, COUNT(start_month), COUNT(submit_time)
FROM t2
WHERE time_rank <= 3
GROUP BY uid, start_month
ORDER BY uid, start_month

WITH t2 AS 
(SELECT uid, date_format(start_time, '%Y%m') start_month
    , DENSE_RANK() OVER(partition by uid order by date_format(start_time, '%Y%m') DESC) time_rank
    , date_format(submit_time, '%Y%m') submit_time
FROM exam_record
WHERE uid in (
SELECT t1.uid
FROM (SELECT uid, (COUNT(score) / COUNT(uid)) rate
        , PERCENT_RANK() OVER(ORDER BY (COUNT(score) / COUNT(uid))) rate_rank
    FROM exam_record er
    JOIN examination_info ei
    ON er.exam_id = ei.exam_id
    WHERE tag = 'SQL'
    GROUP BY uid) t1
JOIN user_info ui
ON ui.uid = t1.uid
WHERE rate_rank <= 0.5
AND `level` in (6,7)
))
SELECT uid, start_month, COUNT(start_month), COUNT(submit_time)
FROM t2
WHERE time_rank <= 3
GROUP BY uid, start_month
ORDER BY uid, start_month