校招算法笔面试 | SQL笔试面试编程题-获得积分最多的人(三)

题目

题目链接

题目描述

我们有两个表：

• user：包含用户信息，包括用户ID id 和用户名 name。
• grade_info：包含积分信息，包括用户ID user_id、积分数量 grade_num 和积分类型 type（add表示增加，reduce表示减少）。

目标是查询出积分最高的用户的ID、名字及其总积分，并按用户ID升序排列。

知识点

• 条件表达式：使用CASE WHEN语句计算每个用户的净积分。
• 窗口函数：使用SUM和DENSE_RANK窗口函数计算每个用户的总积分和排名。
• 连接操作：使用JOIN连接用户表和积分表。
• 排序：使用ORDER BY子句按用户ID升序排列结果。

关键问题分析

1. 计算每个用户的净积分

我们使用CASE WHEN语句计算每个用户的净积分：

select user_id,
       case when type = 'add' then grade_num else 0-grade_num end as grade_num
from grade_info

• CASE WHEN type = 'add' THEN grade_num ELSE 0-grade_num END AS grade_num: 根据积分类型计算净积分，add为增加，否则为减少。

2. 计算每个用户的总积分和排名

我们使用SUM窗口函数计算每个用户的总积分，并使用DENSE_RANK函数按总积分降序排名：

select user_id, sum(grade_num) as grade_num,
       dense_rank() over(order by sum(grade_num) desc) as rk
from (
    --子函数
) s
group by user_id

• SUM(grade_num) AS grade_num: 计算每个用户的总积分。
• DENSE_RANK() OVER (ORDER BY SUM(grade_num) DESC) AS rk: 按总积分降序排名。
• group by user_id：按照用户id分组。

3. 连接用户信息

我们使用JOIN连接用户表和积分表，以获取用户名：

join user on user.id = sub.user_id

• JOIN user ON user.id = sub.user_id: 连接用户信息。

4. 筛选积分最高的用户

我们通过WHERE子句筛选出积分最高的用户：

where rk = 1

• WHERE rk = 1: 筛选出积分最高的用户。

5. 排序输出

我们按用户ID升序排列输出结果：

order by user.id asc

• ORDER BY user.id ASC: 按用户ID升序排列。

完整代码

select user.id, user.name, sub.grade_num
from (
    select user_id, sum(grade_num) as grade_num,
           dense_rank() over(order by sum(grade_num) desc) as rk
    from (
        select user_id,
               case when type = 'add' then grade_num else 0-grade_num end as grade_num
        from grade_info
    ) s
    group by user_id
) sub
join user on user.id = sub.user_id
where rk = 1
order by user.id asc;