题解 | #未完成率较高的50%用户近三个月答卷情况#
未完成率较高的50%用户近三个月答卷情况
https://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c
如果你愿意一层一层一层地剥开我的心。。。
select uid,date_format(start_time,'%Y%m') start_month,count(start_time) total_cnt,sum(if(submit_time is null,0,1)) complete_cnt from exam_record
where (uid,date_format(start_time,'%Y%m')) in
(select t5.uid,start_month from
(select distinct t4.uid,date_format(start_time,'%Y%m') start_month,dense_rank()over(order by date_format(start_time,'%Y%m') desc) ranking from
(select t3.uid from
(select uid from
(select uid,percent_rank()over(order by incomplete_rate desc) ranking from
(select uid,sum(if(submit_time is null,1,0)) incomplete_cnt,count(start_time) total_cnt,sum(if(submit_time is null,1,0))/count(start_time) incomplete_rate from exam_record left join examination_info on exam_record.exam_id = examination_info.exam_id
where tag = 'SQL'
group by uid) t1) t2
where ranking <= 0.5) t3
join user_info on t3.uid = user_info.uid
where level in(6,7)) t4
left join exam_record on t4.uid = exam_record.uid) t5
where ranking <= 3)
group by uid,start_month
order by uid,start_month
北京云智软通信息技术有限公司西安分公司二面9人在聊