题解 | 未完成率top50%用户近三个月答卷情况

with t as(
select r.uid,row_number()over(order by sum(if(r.submit_time is null,1,0))/count(r.start_time) desc) r
from exam_record r join examination_info i using (exam_id)
where i.tag='SQL'
group by r.uid
),  #t表为：求出各个用户未完成率的排名（按降序排序）
t1 as(
select t.uid
from t join user_info i using(uid)
where t.r<=(select (max(t.r)+1)/2 from t) and i.level in (6,7)
) #t1表为：求出较高的50%用户
select a.uid,date_format(a.start_time,"%Y%m") start_month,count(a.start_time) total_cnt,count(submit_time) complete_cnt
from 
     (select uid,start_time,dense_rank()over(partition by uid order by date_format(start_time,"%Y%m") desc) d,submit_time
      from exam_record
     ) a #a表为每个用户做题月份排序（按降序排序）
where a.d<=3 and uid in (select uid from t1)
group by a.uid,start_month
order by a.uid,start_month