题解 | #未完成试卷数大于1的有效用户#
未完成试卷数大于1的有效用户
https://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286
select uid,
# 用0，1分别记录未完成和已完成试卷
sum(case when submit_time is null then 1 else 0 end) incomplete_cnt,
sum(case when submit_time is null then 0 else 1 end) complete_cnt,
group_concat(distinct concat_ws(":",date(start_time),tag) separator ';') detial
from exam_record r,examination_info i
where r.exam_id = i.exam_id and year(start_time) = 2021
group by uid
having complete_cnt >= 1 and incomplete_cnt < 5 and incomplete_cnt > 1
order by incomplete_cnt desc
查看12道真题和解析