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

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

https://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c

一、新的知识点

窗口函数:PERCENT_RANK() 这里一开始花了很多时间去思考如何算出来前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

全部评论

相关推荐

不想投了,不想面了,不想找了感觉自己像个小丑
用微笑面对困难:不是你去大学生就业平台看看啊,boss很多就是冲kpi的
点赞 评论 收藏
分享
评论
点赞
1
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务