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

select uid,start_month,total_cnt,complete_cnt
from
(select 
    uid,
    date_format(start_time,'%Y%m') as start_month,
    rank() over (partition by uid order by date_format(start_time,'%Y%m') desc) as ranking,
    count(start_time) as total_cnt,
    count(submit_time) as complete_cnt
from exam_record
group by uid,date_format(start_time,'%Y%m')) tb3
join
(select uid 
from
    (select 
         uid,
         percent_rank() over(order by sum(if(score is null,1,0)) /count(start_time) desc) as rnk
     from exam_record er1
     group by uid) tb1 
join user_info using(uid)
where rnk <= 0.5 and level in (6,7))tb2 using(uid)
where ranking <= 3
order by uid,start_month