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

select uid,
    sum(if(score is not null and submit_time is not null,0,1)) incomplete_cnt,
    sum(if(score is not null and submit_time is not null,1,0)) complete_cnt,
    group_concat(distinct concat(date(start_time),':',tag) order by start_time asc SEPARATOR ';' )

from examination_info ei 
left join exam_record er using(exam_id)
where start_time >='2021-01-01'
group by 1
having incomplete_cnt<5 and complete_cnt>=1 and incomplete_cnt>1
order by incomplete_cnt desc