题解 | #未完成率较高的50%用户近三个月答卷情况#

select
    uid,
    start_month,
    count(start_month) as total_cnt,
    count(score) as complete_cnt
from
    (select 
        exam_record.uid,
        date_format(exam_record.start_time,'%Y%m') as start_month,
        exam_record.score,
        dense_rank() over (partition by uid order by date_format(exam_record.start_time,'%Y%m') desc) as ranks
    from exam_record
    join 
        (select *
        from 
             (select *, percent_rank() over (order by incomplete_rate desc) as percent_ranks
             from
                    (select 
                        uid,
                        sum(if(exam_record.submit_time is null AND examination_info.tag = 'SQL',1,0)) as incomplete_cnt,
                        sum(if(examination_info.tag = 'SQL',1,0)) as total_cnt,
                        round(sum(if(exam_record.submit_time is null AND examination_info.tag = 'SQL',1,0))/sum(if(examination_info.tag = 'SQL',1,0)),4) as incomplete_rate
                    from exam_record
                    join examination_info
                    using (exam_id)
                    group by exam_record.uid) as incomplete) as incomplete_rank
        join user_info
        using (uid)
        where incomplete_rank.percent_ranks <= 0.5 AND user_info.level between 6 and 7) as users
    using (uid)) as exams
where ranks <=3
group by uid, start_month
order by uid, start_month