题解 | #未完成试卷数大于1的有效用户#
未完成试卷数大于1的有效用户
https://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286
with data_f_use as (
select uid, exam_id, if(submit_time is null, 1, 0) as incomplete,
if(submit_time is null, 0, 1) as complete, start_time
from exam_record
where year(start_time) = 2021
)
select uid, sum(incomplete) as incomplete_cnt,
sum(complete) as complete_cnt,
group_concat(distinct concat_ws(':', substr(start_time, 1, 10), tag) separator ';')
from data_f_use
left join examination_info using (exam_id)
group by uid
having (incomplete_cnt between 2 and 4) and complete_cnt >= 1
order by incomplete_cnt desc
