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

select uid,
		start_month,
        count(start_time) as total_cnt,
		count(submit_time) as 	complete_cnt
from
(
select *,date_format(start_time,'%Y%m') as start_month,
	   dense_rank() over (partition by uid order by date_format(start_time,'%y%m') desc) as drk
from exam_record
) q
where drk<=3
and uid in 
(
select uid
from
(
select er.uid,
		`level`,
-- 		count(start_time) as total_cnt1,
-- 		count(start_time)-count(submit_time) as incomplete_cnt1,
-- 		(count(start_time)-count(submit_time))/count(start_time) asincomplete_rate,
		percent_rank( ) over (order by (count(start_time)-count(submit_time))/count(start_time)) as pr
from exam_record as er left join examination_info as ei on er.exam_id = ei.exam_id
		 left join user_info as ui on ui.uid = er.uid 
where ei.tag ='SQL'
group by er.uid
) qq
where pr>=0.5 and `level` in (6,7)
)
group by uid,start_month#因为select出现了uid和start_time所以分组对两列分，否则会报错。
order by uid,start_month;