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

select uid, count(*) - count(submit_time) as incomplete_cnt,
    count(submit_time) as complete_cnt,
    group_concat(distinct concat_ws(':',date(start_time),tag) separator ';') as detail
from exam_record
join examination_info using(exam_id)
where year(start_time) = 2021
group by uid
having count(submit_time) >= 1 
and (count(*) - count(submit_time))  between 2 and 4
order by incomplete_cnt desc;