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

-- 子查询计算完成试卷数，未完成试卷数
-- 按照uid分组，统计用户个人总的完成试卷数，未完成试卷数
-- HAVING筛选出有效用户
-- 有效用户指完成试卷作答数至少为1且未完成数小于5
SELECT 
uid,
SUM(incomplete) AS incomplete_cnt,
SUM(complete) AS complete_cnt,
GROUP_CONCAT(distinct CONCAT_WS(':', time, tag) SEPARATOR ';') as detail
FROM  (
SELECT 
uid,
exam_id,
ANY_VALUE(DATE_FORMAT(start_time,"%Y-%m-%d")) AS time,
tag,
COUNT(start_time) - count(submit_time) AS incomplete,
COUNT(submit_time) AS complete
FROM exam_record
LEFT JOIN examination_info USING(exam_id)
WHERE YEAR(start_time) = '2021'
GROUP BY uid,exam_id,time
) AS t1
GROUP BY uid
HAVING complete_cnt >= 1 AND incomplete_cnt > 1 AND
incomplete_cnt < 5
ORDER BY incomplete_cnt DESC