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

select uid,start_month,count(start_month),count(submit_month)
from 
(select uid,date_format(start_time,'%Y%m') start_month,date_format(submit_time,'%Y%m') submit_month,
       dense_rank() over(partition BY uid order by date_format(start_time,'%Y%m') desc) ranking
from exam_record) data2
where ranking <4 and uid in 
                (select uid
                from 
                (select uid,level,sum(if(submit_time is null,1,0))/count(start_time) incomplete_rate,
                    PERCENT_RANK() over(ORDER BY sum(if(submit_time is null,1,0))/count(start_time) DESC, uid)  incomp_rate_rank
                from exam_record
                join examination_info using(exam_id)
                join user_info using(uid)
                where tag='SQL'
                group by uid) data1
                where incomp_rate_rank<=0.5 and level in (6,7)
                )
group by uid,start_month
order by uid,start_month