题解 | #注册当天就完成了试卷的名单第三页#
注册当天就完成了试卷的名单第三页
https://www.nowcoder.com/practice/718d36d2667b48faa2168b6c1521816a
题目:找到求职方向为算法工程师,且注册当天就完成了算法类试卷的人,按参加过的所有考试最高得分排名。排名榜很长,我们将采用分页展示,每页3条,现在需要你取出第3页(页码从1开始)的人的信息。
分解下条件:
- 求职方向为算法工程师且注册当天就完成了算法类试卷的人
- 参加过的所有考试最高得分排名
- 排名榜很长,采用分页展示,每页3条
思路:
根据三个表进行关联,过滤求职方向为算法工程师这类人的信息,根据uid,tag进行开窗取出每个用户在每个类完成考试的最早时间,再根据uid进行开窗取出每个用户在所有考试的最大分数
select a1.uid, min(a1.submit_time) over ( partition by a1.uid ,a3.tag ) min_submit_time, max(a1.score) over ( partition by a1.uid ) max_score, a2.register_time, a2.level, a3.tag from exam_record a1 left join user_info a2 on a1.uid = a2.uid left join examination_info a3 on a1.exam_id = a3.exam_id where a2.job = '算法'
第二步根据类别过滤做过算法试卷的且最早做试卷的时间为注册当天的人的试卷记录
过滤条件:tag = '算法' and date_format (register_time, '%Y-%m-%d') = date_format (min_submit_time, '%Y-%m-%d')
再根据uid,level,register_time,max_score分组进行去重
最后一步根据用户在每个试卷类别下最大分数进行降序排序,将序号除以3(每页3条),并向上取整获取页数
ceiling(row_number() over (order by max_score desc) / 3)
完整代码如下:
with exam_info as ( select *, ceiling(row_number() over (order by max_score desc) / 3) page from ( select uid, level, register_time, max_score from ( select a1.uid, min(a1.submit_time) over ( partition by a1.uid ,a3.tag ) min_submit_time, max(a1.score) over ( partition by a1.uid ) max_score, a2.register_time, a2.level, a3.tag from exam_record a1 left join user_info a2 on a1.uid = a2.uid left join examination_info a3 on a1.exam_id = a3.exam_id where a2.job = '算法' ) t where tag = '算法' and date_format (register_time, '%Y-%m-%d') = date_format (min_submit_time, '%Y-%m-%d') group by uid, level, register_time, max_score ) t ) select uid, level, register_time, max_score from exam_info where page = 3