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

select uid,	# 用户ID
	count(start_time)-count(submit_time) incomplete_cnt,	# 未完成试卷作答数
	count(submit_time) complete_cnt,	# 完成试卷作答数
	group_concat(distinct tag_time separator ';') detail	# 试卷{日期:tag}集合
from(
	select uid,start_time,submit_time,
		concat_ws(':',left(start_time,10),tag) tag_time
	from exam_record er inner join examination_info ei
		on er.exam_id = ei.exam_id
	where year(start_time) = 2021	# 统计2021年
	order by start_time
	) as 提取数据
group by uid
having count(submit_time) >= 1 # 有效用户指完成试卷作答数至少为1且未完成数小于5
	and count(start_time)-count(submit_time) < 5
	and count(start_time)-count(submit_time) > 1	# 每个未完成试卷作答数大于1的有效用户 
order by count(start_time)-count(submit_time) desc;	# 按未完成试卷数量由多到少排序