题解 | #未完成率较高的50%用户近三个月答卷情况#
未完成率较高的50%用户近三个月答卷情况
https://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c
一、新的知识点
二、思路
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