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

select
    t.uid,
    t.start_month,
    count(t.start_month) as total_cnt,
    count(t.score) as complete_cnt
from (
select
    uid,
    date_format(date(start_time),'%Y%m') start_month ,
    score,
    dense_rank() over(partition by uid order by date_format(date(start_time),'%Y%m') desc) dr
from
exam_record) t
where t.dr<=3
and t.uid
in
(select
t.uid
from(
select
    er.uid,
    percent_rank() over(order by count(if(er.score is null,1,null))/count(er.start_time) desc ) as pr
from exam_record er
inner join examination_info ei
    on er.exam_id=ei.exam_id
where ei.tag='SQL'
group by er.uid) t
where pr<=0.5
    and t.uid in
(select
	uid
from user_info
where level in (6,7)))
group by t.uid,t.start_month
order by t.uid,t.start_month;