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

select uid,date_format(start_time,'%Y%m') start_month,count(ranking) total_cnt,count(ifnull(submit_time,null)) complete_cnt from
 (
 select b.uid,start_time,submit_time,dense_rank()over(partition by b.uid order by date_format(start_time,'%Y%m') desc) ranking
from exam_record a left join user_info b on a.uid = b.uid
where b.uid in (
 select uid from (
select c.uid,(count(*)-count(ifnull(submit_time,null)))/count(*) incomplete_rate,
percent_rank() over (order by avg(case when submit_time is null then 1 else 0 end) desc) pr 
from examination_info a,exam_record b,user_info c 
where a.exam_id = b.exam_id and b.uid = c.uid
and tag = 'SQL'
group by c.uid
) d
where pr <=0.5
 ) 
 and (level = '6' or level = '7')
 ) e 
where ranking <= 3
group by uid,date_format(start_time,'%Y%m')
order by uid,start_month;