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

select uid,date_format(start_time,"%Y%m") 'start_time_1',count(start_time),count(score)
from 
(select uid, start_time 'start_time',submit_time,score,dense_rank()over( partition by uid order by date_format(start_time,"%Y%m") desc)  'ranking'
from exam_record 
where uid in (
    select uid
from 
(select uid,count(score),count(start_time),count(score)/count(start_time),percent_rank()over(order by count(score)/count(start_time) desc) 'incomplete_rate_ranking'
from exam_record r left join examination_info i on r.exam_id = i.exam_id
where i.tag = 'SQL'
group by uid
) t1
where t1.incomplete_rate_ranking >=0.5 and uid in (
    select uid
    from user_info
    where level =7 or level = 6
))) t2
where ranking<=3 
group by uid,start_time_1
order by uid asc,start_time_1 asc