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

select ee.uid,ee.start_month
,count(ee.uid) as total_cnt
,count(ee.score) as complete_cnt
from 
(select e.uid
,e.start_time,e.score,u.level
,date_format(start_time,"%Y%m") as start_month
,dense_rank() over(partition by uid order by date_format(start_time,"%Y%m") desc) as rk
from exam_record as e left join user_info as u using(uid)) as ee
where ee.uid in 
(select uid from
(select uid
,percent_rank() over (order by round(count(score)/count(uid),4)) as rk
from exam_record
left join examination_info using(exam_id)
where tag="SQL" group by uid) as t0 where rk<=0.5)
and ee.level > 5
and ee.rk<4
group by ee.uid,ee.start_month
order by ee.uid,ee.start_month