Hackerrank高级SQL认证：各竞赛Top3参赛者排名查询问题

题目要求 #

• 业务场景：平台举办多场竞赛，每位参赛者可在单场竞赛中多次提交答题
• 有效成绩规则：排名仅统计每位参赛者在对应竞赛中的最高得分，多次提交取最高分作为个人最终成绩
• 排名规则：同一场竞赛内得分相同的参赛者排名一致，采用并列连续排名逻辑
• 输出规则：
  • 返回每个竞赛排名前三的参赛者信息，输出字段包含event_id、第1名姓名列表、第2名姓名列表、第3名姓名列表
  • 同一名次下存在多个参赛者时，姓名需按字母顺序升序排列后用逗号分隔拼接
  • 最终查询结果按event_id升序排列

解题思路 #

1. 先做有效成绩清洗：按竞赛ID、参赛者维度分组，提取每个参赛者在单场竞赛的最高得分，过滤掉多次提交的低分无效记录
2. 计算名次：以竞赛为分区，对有效成绩按得分降序做密集排名（DENSE_RANK()），保证同分用户名次相同，且名次连续无跳号，能完整覆盖1、2、3三个名次档位
3. 名次维度聚合：按竞赛ID、名次分组，将同组内的参赛者姓名按字母序排序后，用字符串聚合函数拼接成逗号分隔的列表
4. 行转列输出：将每个竞赛下1、2、3名的姓名列表分别转为独立列，最终按竞赛ID升序返回结果

参考SQL代码 #

代码基于MySQL 8.0及以上版本编写，兼容支持窗口函数的主流SQL环境，PostgreSQL环境替换对应字符串聚合函数即可

WITH user_max_score AS (
    -- 提取每个用户在单场竞赛的最高有效得分
    SELECT
        event_id,
        user_name,
        MAX(score) AS best_score
    FROM submissions
    GROUP BY event_id, user_name
),
user_ranking AS (
    -- 按竞赛分区计算密集排名，同分同名次且排名连续
    SELECT
        event_id,
        user_name,
        DENSE_RANK() OVER (PARTITION BY event_id ORDER BY best_score DESC) AS place_rank
    FROM user_max_score
),
rank_name_concat AS (
    -- 聚合同竞赛同名次的姓名，按字母序排序后拼接
    SELECT
        event_id,
        place_rank,
        GROUP_CONCAT(user_name ORDER BY user_name SEPARATOR ',') AS name_list
        -- PostgreSQL环境替换为：STRING_AGG(user_name, ',' ORDER BY user_name) AS name_list
    FROM user_ranking
    WHERE place_rank <= 3
    GROUP BY event_id, place_rank
)
-- 行转列得到最终结果
SELECT
    event_id,
    MAX(CASE WHEN place_rank = 1 THEN name_list END) AS first_place_names,
    MAX(CASE WHEN place_rank = 2 THEN name_list END) AS second_place_names,
    MAX(CASE WHEN place_rank = 3 THEN name_list END) AS third_place_names
FROM rank_name_concat
GROUP BY event_id
ORDER BY event_id ASC;

关键注意点 #

• 禁止使用RANK()替代DENSE_RANK()：普通排名函数在出现并列名次时会跳号，比如2人并列第1时，下一个名次会直接变为第3，导致第2名数据缺失，不符合题目要求
• 字符串聚合时必须在聚合函数内指定姓名排序规则，否则数据库默认返回顺序不确定，无法满足同名次姓名按字母序排列的要求
• 若某竞赛不存在对应档位的名次（比如所有参赛者得分仅分2档，无第3名），对应姓名字段会返回NULL，符合业务逻辑

内容的提问来源于stack exchange，提问作者Ruchi Sharma