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

#先创建满足要求的用户id
with t1 as (
select
    tmp1.*
from(select
        uid
        ,exam_id
        ,substr(start_time,1,10) as ti
        ,count(exam_id) over(partition by uid) as cnt
        ,count(submit_time) over(partition by uid) as com_cnt
        ,count(if(submit_time is null,1,null)) over(partition by uid) as incom_cnt
    from exam_record
    where year(start_time)='2021'
    )tmp1
where tmp1.com_cnt>=1 and tmp1.incom_cnt>1 and tmp1.incom_cnt<5
)
select
    tmp1.uid
    ,max(tmp1.incom_cnt) as incomplete_cnt
    ,max(tmp1.com_cnt) as complete_cnt
    ,group_concat(distinct tmp1.tag order by tmp1.ti,tmp1.exam_id SEPARATOR ';') as detail
from(select 
        t1.*
        ,concat_ws(':',t1.ti,e.tag) as tag
    from t1 join examination_info e
    on t1.exam_id = e.exam_id
    )tmp1
group by tmp1.uid
order by incomplete_cnt desc