题解 | 未完成试卷数大于1的有效用户

WITH t AS (
    SELECT
        r.uid,
        CONCAT(DATE(r.start_time), ':', i.tag) AS detail_item,
        CASE WHEN r.submit_time IS NULL THEN 1 ELSE 0 END AS incomplete,
        CASE WHEN r.submit_time IS NULL THEN 0 ELSE 1 END AS complete
    FROM exam_record r
    JOIN examination_info i
        ON r.exam_id = i.exam_id
    WHERE r.start_time >= '2021-01-01'
      AND r.start_time <  '2022-01-01'
)
SELECT
    uid,
    SUM(incomplete) AS incomplete_cnt,
    SUM(complete) AS complete_cnt,
    GROUP_CONCAT(
        DISTINCT detail_item
        ORDER BY detail_item
        SEPARATOR ';'
    ) AS detail
FROM t
GROUP BY uid
HAVING
    SUM(incomplete) > 1
    AND SUM(incomplete) < 5
    AND SUM(complete) >= 1
ORDER BY incomplete_cnt DESC;