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

 步骤：
 1.计算用户数据，提取有效信息，聚合作答记录
 2.筛选有效用户：指完成试卷作答数至少为1且未完成数小于5
 3.筛选未完成试卷作答数大于1的有效用户
 难点：多行转一行
 函数group_cancat( distinct [字段] order by separator '')  聚合不同的作答记录（一次）
 字符串函数concat(date(start_time),':',tag) 组合作答时间和类别为一记录
 group_concat(distinct concat(date(start_time),':',tag) order by date(start_time), field(tag, 'SQL','算法') separator ';') as detail

select -- 3. 筛选未完成试卷作答数大于1的有效用户
    uid
    , incomplete_cnt
    , complete_cnt
    , detail
from
( 1. 提取和计算用户数据
    select
        distinct uid
        , sum(if(submit_time is null, 1, 0)) as incomplete_cnt
        , count(submit_time) as complete_cnt
        , group_concat(distinct concat(date(start_time),':',tag) order by date(start_time), field(tag, 'SQL','算法') separator ';') as detail
    from examination_info a 
    join exam_record b
    on a.exam_id=b.exam_id
    where year(start_time)='2021'
    group by uid
    having count(start_time)>=1 
        and sum(if(submit_time is null, 1, 0))<5 -- 2.有效用户指完成试卷作答数至少为1且未完成数小于5
    order by incomplete_cnt desc
) t1
where incomplete_cnt>1
order by incomplete_cnt desc