SQL37 0级用户高难度试卷的平均用时和平均得分

题目主要信息：

• 输出每个0级用户所有的高难度试卷考试平均用时和平均得分
• 平均用时保留一位小数，平均得分保留整数
• 未完成的默认试卷最大考试时长和 0 分处理

问题拆解：

• 通过 WITH AS 语句筛选出符合要求的用户和考试信息，存入临时表中供接下来使用。知识点：WITH AS
  • 筛选出 level=0 的用户信息
  • 筛选出 difficulty="hard" 的考试信息
• 筛选出 exam_record 中 uid 和 exam_id 均满足条件的数据。知识点：INNER JOIN, IN
  • 筛选 uid 的方式为在 WHERE 语句中判断 uid IN valid_u
  • 筛选 exam_id 的方式为将 exam_record 和 valid_e 做 INNER JOIN
• 对获取的结果基于 uid 进行聚合统计。知识点：AVG, ROUND, GROUP BY, TIMESTAMPDIFF
  • AVG(score) 统计出用户的平均分。
  • 获取考试时长的方法为，统计 submit_time 和 start_time 的时间差。TIMESTAMPDIFF(MINUTE, start_time, submit_time) 可以获取分钟维度的作答时长。
  • 通过 ROUND 函数对统计结果保留有效位数（默认保留整数）

代码：

WITH
valid_u AS (SELECT uid FROM user_info WHERE level=0),
valid_e AS (SELECT exam_id, duration FROM examination_info WHERE difficulty="hard")
SELECT
    uid,
    ROUND(AVG(IF(score IS NULL, 0, score))) AS avg_score,
    ROUND((AVG(IF(submit_time IS NULL, duration,
           TIMESTAMPDIFF(MINUTE, start_time, submit_time)))), 1) AS avg_time_took
FROM
    exam_record
JOIN
    valid_e
ON
    exam_record.exam_id = valid_e.exam_id
WHERE
    uid IN (SELECT * FROM valid_u)
    AND duration IS NOT NULL
GROUP BY
    uid