SQL173 月均完成试卷数不小于3的用户爱作答的类别

描述

现有试卷作答记录表exam_record（uid：用户ID, exam_id：试卷ID, start_time：开始作答时间, submit_time：交卷时间，没提交的话为NULL, score：得分），示例数据如下：

试卷信息表examination_info（exam_id：试卷ID, tag：试卷类别, difficulty：试卷难度, duration：考试时长, release_time：发布时间），示例数据如下：

有一批用户做试卷较多，他们会在某一个月内（试卷的开始和结束时间都在同一个月）做三张及以上（≥3）试卷，公司准备统计这些用户喜欢做的试卷的类别，以及每个类别中所有用户一共作答的次数，按照次数降序输出，示例输出如下：

解释：用户1002和1005在2021年09月的完成试卷数目均为3，其他用户均小于3；然后用户1002和1005作答过的试卷tag分布结果按作答次数降序排序依次为C++、SQL、算法。

 -- 第一步：找出在某个月完成 ≥3 次且 start/submit 在同一个月的用户
WITH
    active_users AS (
        SELECT
            er.uid
        FROM
            exam_record er
            JOIN examination_info ei USING (exam_id)
        WHERE
            er.submit_time IS NOT NULL
            AND DATE_FORMAT(er.start_time, '%Y-%m') = DATE_FORMAT(er.submit_time, '%Y-%m')
        GROUP BY
            er.uid,
            DATE_FORMAT(er.start_time, '%Y-%m')
        HAVING
            COUNT(*) >= 3
    )
    -- 第二步：统计这些用户所有作答过的试卷的 tag 次数
SELECT
    ei.tag,
    COUNT(*) AS tag_cnt
FROM
    exam_record er
    JOIN examination_info ei USING (exam_id)
WHERE
    er.uid IN (
        SELECT
            uid
        FROM
            active_users
    )
GROUP BY
    ei.tag
ORDER BY
    tag_cnt DESC;

🔍 分步解析

✅ Step 1：定义 CTE —— active_users

WITH active_users AS (
    SELECT er.uid
    FROM exam_record er
    JOIN examination_info ei USING (exam_id)
    WHERE 
        er.submit_time IS NOT NULL                -- 只看“已完成”的考试
        AND DATE_FORMAT(er.start_time, '%Y-%m') = DATE_FORMAT(er.submit_time, '%Y-%m')  -- 同月完成
    GROUP BY er.uid, DATE_FORMAT(er.start_time, '%Y-%m')  -- 按用户+月份分组
    HAVING COUNT(*) >= 3                          -- 某月完成 ≥3 次
)

📌 关键点：

• GROUP BY uid, 月份：确保是“某个月”完成 3 次
• IN (SELECT uid FROM active_users)：只要用户在任意一个月满足条件，就算活跃用户
• JOIN examination_info：虽然这里没用到 tag，但 USING(exam_id) 无副作用

✅ 正确！即使 ei 在 CTE 中未使用字段，也不影响结果。

✅ Step 2：统计这些用户所有作答的 tag

SELECT
    ei.tag,
    COUNT(*) AS tag_cnt
FROM exam_record er
JOIN examination_info ei USING (exam_id)
WHERE er.uid IN (SELECT uid FROM active_users)  -- 筛选活跃用户
GROUP BY ei.tag
ORDER BY tag_cnt DESC;

📌 关键点：

• 这里统计的是 所有作答记录（包括 submit_time IS NULL 的未完成）
• 因为外层 exam_record 没有加 submit_time IS NOT NULL
• 所以是“这些活跃用户接触过的所有试卷类型”