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

-- 第一步：筛选出SQL试卷未完成率较高的50%的用户
with t as 
(
    select uid
    from 
    (select *,row_number()over(order by incomplete_rate desc) as rn,count(1)over() as num
    from
    (select uid,count(1)-count(submit_time) as incomplete_cnt,count(1) as total_cnt,(count(1)-count(submit_time))/count(1) as incomplete_rate
    from exam_record
    where exam_id in (select exam_id from examination_info where tag = 'SQL')
    group by uid) t1) t2
    where rn<=(num+1)/2 and uid in (select uid from user_info where level in ('6','7') )
)
-- 第二步:冲
select uid,dtime as start_month,count(1) as total_cnt,count(score) as complete_cnt
from 
(select uid,DATE_FORMAT(start_time,"%Y%m") as dtime,DENSE_RANK()over(partition by uid order by DATE_FORMAT(start_time,"%y%m") desc) as rn,score
from exam_record
where uid in (select uid from t)) t1
where rn<=3
group by uid,start_month
order by uid,start_month