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

select uid,
			 date_format(start_time,'%Y%m') as start_month,
			 count(start_time) as total_cnt,
			 count(submit_time) as complete_cnt
from ( -- 6、7级用户在近三个月有试卷作答记录的用户 #uid集合为1001、1002
			select uid,
						 start_time,
						 submit_time
		  from (
						select er.uid,
									 start_time,
									 submit_time,
									 dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) as recent_month
						from exam_record as er
						left join user_info as ui
						on ui.uid=er.uid
						where level>=6  #这一步筛选掉了1003
						) as recent_table
			where recent_month<=3
			) as final_table
where uid in ( -- SQL试卷上未完成率较高的50%用户uid集合 -- #uid集合为1002、1003
							select uid
							from (
										select uid,
													 total_cnt,
													 incomplete_rate,
													 rank_num
										from (
													select uid,
																 incomplete_cnt,
																 total_cnt,
																 incomplete_cnt/total_cnt as incomplete_rate,
																 row_number() over(order by incomplete_cnt/total_cnt desc) as rank_num
													from (
																select uid,
																			 count(if(submit_time is null,1,null)) as incomplete_cnt,
																			 count(start_time) as total_cnt
																from exam_record as er
																left join examination_info as ei
																on ei.exam_id=er.exam_id
																where tag='SQL'
																group by uid
																) as inner_table
												 ) as table1
									 where rank_num<=ceiling((select count(distinct uid)/2 from exam_record))
									   ) as connect_table
								 )
group by uid,start_month
order by uid asc,start_month asc;