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

select t4.uid, extract(year_month from t4.start_time) as start_month, count(t4.start_time) as total_cnt, count(t4.submit_time) as complete_cnt from
(select er.uid, er.start_time, er.submit_time, dense_rank() over(order by extract(year_month from er.start_time) desc) as dr from (select t3.uid from (select t2.uid from (select uid, cume_dist() over(order by incomplete_rate) as rn from (select uid, (count(start_time) - count(submit_time)) as incomplete_cnt, count(start_time) as total_cnt, ((count(start_time) - count(submit_time)) / count(start_time)) as incomplete_rate from examination_info ei inner join exam_record er on ei.exam_id=er.exam_id where tag='SQL' and start_time is not null group by uid) t1) t2 where t2.rn > 0.5) t3 inner join user_info ui on t3.uid=ui.uid where ui.level in (7,6)) t3 inner join exam_record er on t3.uid=er.uid) t4 where t4.dr in (1,2,3) group by t4.uid,extract(year_month from t4.start_time) order by uid,start_month;