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

select
  uid,
  sum(if(submit_time is Null, 1, 0)) as incomplete_cnt,
  sum(if(submit_time is not Null, 1, 0)) as complete_cnt,
  group_concat(
    distinct concat_ws(':', date(start_time), tag) SEPARATOR ';'
  ) as detail
from
  exam_record
  join examination_info on exam_record.exam_id = examination_info.exam_id
where
  year(start_time) = "2021"
group by
  uid
having
  complete_cnt >= 1
  and incomplete_cnt < 5
  and incomplete_cnt > 1
order by
  incomplete_cnt desc