题目主要信息：

  • 当有 0 级用户未完成试卷数大于 2 时输出每个 0 级用户的试卷未完成数和未完成率（保留3位小数）
  • 若不存在这样的用户，则输出所有有作答记录的用户的这两个指标
  • 结果按未完成率升序排序

问题拆解：

  • 通过 WITH AS 语句筛选出未完成试卷数大于 2 的 0 级用户，并判断这类用户是否存在。知识点：IS NULL, COUNT, HAVING, WITH AS
    • 通过 WHERE 语句判断 level=0 且 submit_time IS NULL
    • 基于 uid 对符合条件的 exam_record 进行聚合，统计 COUNT(1) 作为该用户的未完成试卷总数
    • 使用 HAVING 语句筛选出未完成试卷总数大于 2 的用户
    • 最终统计符合要求的用户数量存入临时表
  • 统计所有用户的未完成数、未完成率信息，通过 WITH AS 存入临时表。知识点：LEFT JOIN, IS NOT NULL, IF, SUM, COUNT
    • 将 user_info LEFT JOIN exam_record 获得总表
    • 基于 uid 对总表数据进行聚合统计，IF(submit_time IS NULL AND start_time IS NOT NULL, 1, 0) 判断该记录是否未完成，对其进行 SUM 操作得到未完成总数。
    • COUNT(1) 获得用户的总提交数，二者做除法获得未完成率。
  • 通过 JOIN 获取上述两表的笛卡尔积，根据表一中的条件判断展现哪部分用户的汇总信息。此时需要通过 ROUND 对未完成率保留三位小数。知识点：JOIN, ORDER BY, ROUND

代码：

WITH target_user AS (
    SELECT user_info.uid, COUNT(1) AS incomplete_cnt
    FROM exam_record LEFT JOIN user_info
    ON exam_record.uid = user_info.uid
    WHERE user_info.level = 0 AND submit_time IS NULL
    GROUP BY user_info.uid
    HAVING incomplete_cnt > 2
), target_user_exist AS (SELECT COUNT(1) AS `exist` FROM target_user)
, total_summary AS (
    SELECT
        user_info.uid,
        MAX(user_info.level) AS level,
        SUM(IF(submit_time IS NULL AND start_time IS NOT NULL, 1, 0)) AS incomplete_cnt,
        SUM(IF(submit_time IS NULL AND start_time IS NOT NULL, 1, 0)) / COUNT(1) AS incomplete_rate,
        SUM(IF(start_time IS NOT NULL, 1, 0)) AS has_submit
    FROM user_info LEFT JOIN exam_record
    ON user_info.uid = exam_record.uid
    GROUP BY user_info.uid
)
SELECT
    uid,
    incomplete_cnt,
    ROUND(incomplete_rate, 3)
FROM total_summary LEFT JOIN target_user_exist ON 1=1
WHERE (exist=0 AND has_submit>0) OR (exist=1 AND level=0)
ORDER BY incomplete_rate ASC