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

select 
    uid,
    max(score_null_count) as incomplete_cnt,
    max(score_notnull_count) as complete_cnt,
    group_concat(distinct 
    concat(date_format(start_time,'%Y-%m-%d'),':',tag) 
    order by start_time separator';') as detail
from
(
    SELECT 
        uid,
        examination_info.exam_id,
        start_time,
        tag,
        sum(if(submit_time IS NULL,1,0)) over(partition by uid) as score_null_count,
        sum(if(submit_time IS NULL,0,1)) over(partition by uid) as score_notnull_count
    FROM exam_record 
    inner join examination_info
    on exam_record.exam_id = examination_info.exam_id
    WHERE YEAR(start_time)=2021
)as contain_score_nullinfo_t_1
where score_null_count<5 and score_notnull_count>=1 and score_null_count>1
group by contain_score_nullinfo_t_1.uid
order by incomplete_cnt desc