HackerRank SQL高级题：only full group by模式下MySQL查询语法错误修复

原有代码错误原因 #

• 语法错误：子查询中ORDER BY位置放在GROUP BY之前，SQL执行顺序要求先执行GROUP BY聚合，再执行ORDER BY排序，顺序颠倒直接触发1064语法报错。
• 逻辑错误1：现有COUNT(DISTINCT hacker_id)统计的是当日所有有提交记录的黑客总数，不符合题目要求的「从竞赛首日到当日连续每天都有至少1次提交」的统计规则。
• 逻辑错误2：每日提交次数统计逻辑错误，聚合计数需要在GROUP BY后直接计算，不能通过变量赋值的方式取数，且排序规则缺失：需要先按提交次数倒序，次数相同时按hacker_id升序，才能取到符合要求的黑客。
• 逻辑错误3：外层查询没有按日期分组，也没有过滤竞赛的日期范围，输出结果会出现冗余数据。

正确查询语句（MySQL 8.0+） #

WITH 
-- 生成15天的竞赛日期序列，避免当日无任何提交时漏数据
contest_dates AS (
    SELECT DATE('2016-03-01') + INTERVAL (t.n) DAY AS submission_date
    FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
        UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
    ) t
),
-- 统计每个黑客每日的提交次数
daily_submit_cnt AS (
    SELECT 
        submission_date,
        hacker_id,
        COUNT(submission_id) AS submit_cnt
    FROM Submissions
    WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15'
    GROUP BY submission_date, hacker_id
),
-- 标记连续提交的黑客：每个黑客的提交日期按升序编号，若编号 = 日期与首日的差+1，说明从首日到当日连续提交
consecutive_hackers AS (
    SELECT
        submission_date,
        hacker_id,
        DATEDIFF(submission_date, '2016-03-01') + 1 = ROW_NUMBER() OVER (PARTITION BY hacker_id ORDER BY submission_date) AS is_consecutive
    FROM daily_submit_cnt
),
-- 统计每日连续提交的黑客总数
daily_consecutive_cnt AS (
    SELECT
        submission_date,
        COUNT(DISTINCT hacker_id) AS consecutive_hacker_cnt
    FROM consecutive_hackers
    WHERE is_consecutive = TRUE
    GROUP BY submission_date
),
-- 给每日的黑客按提交次数倒序、id升序排序，取第一位
daily_top_hacker AS (
    SELECT
        submission_date,
        hacker_id,
        ROW_NUMBER() OVER (PARTITION BY submission_date ORDER BY submit_cnt DESC, hacker_id ASC) AS rn
    FROM daily_submit_cnt
)
SELECT
    cd.submission_date,
    COALESCE(dcc.consecutive_hacker_cnt, 0) AS consecutive_hacker_cnt,
    dth.hacker_id,
    h.name
FROM contest_dates cd
LEFT JOIN daily_consecutive_cnt dcc ON cd.submission_date = dcc.submission_date
LEFT JOIN daily_top_hacker dth ON cd.submission_date = dth.submission_date AND dth.rn = 1
LEFT JOIN Hackers h ON dth.hacker_id = h.hacker_id
ORDER BY cd.submission_date ASC;

MySQL 5.x兼容版本（无窗口函数场景） #

-- 初始化变量
SET @prev_hacker = NULL, @rn = 0, @prev_date = NULL, @top_rn = 0;

SELECT 
    t.submission_date,
    t.consecutive_cnt,
    t.hacker_id,
    h.name
FROM (
    -- 关联连续提交数和当日top黑客数据
    SELECT
        a.submission_date,
        a.consecutive_cnt,
        b.hacker_id
    FROM (
        -- 计算每日连续提交的黑客数
        SELECT
            submission_date,
            COUNT(DISTINCT hacker_id) AS consecutive_cnt
        FROM (
            SELECT
                submission_date,
                hacker_id,
                -- 给每个黑客的提交日期按顺序编号
                @rn := IF(@prev_hacker = hacker_id, @rn + 1, 1) AS row_num,
                @prev_hacker := hacker_id
            FROM (
                SELECT DISTINCT submission_date, hacker_id
                FROM Submissions
                WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15'
                ORDER BY hacker_id, submission_date
            ) t1
        ) t2
        WHERE DATEDIFF(submission_date, '2016-03-01') + 1 = row_num
        GROUP BY submission_date
    ) a
    LEFT JOIN (
        -- 取每日提交最多且id最小的黑客
        SELECT
            submission_date,
            hacker_id
        FROM (
            SELECT
                submission_date,
                hacker_id,
                @top_rn := IF(@prev_date = submission_date, @top_rn + 1, 1) AS rn,
                @prev_date := submission_date
            FROM (
                SELECT
                    submission_date,
                    hacker_id,
                    COUNT(submission_id) AS cnt
                FROM Submissions
                WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15'
                GROUP BY submission_date, hacker_id
                ORDER BY submission_date, cnt DESC, hacker_id ASC
            ) t3
        ) t4
        WHERE rn = 1
    ) b ON a.submission_date = b.submission_date
) t
LEFT JOIN Hackers h ON t.hacker_id = h.hacker_id
ORDER BY t.submission_date ASC;

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