题解 | #未完成率较高的50%用户近三个月答卷情况#
未完成率较高的50%用户近三个月答卷情况
https://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c
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;