SQL31

SELECT uid, start_month, COUNT(start_time) AS total_cnt, COUNT(submit_time) AS complete_cnt FROM( SELECT er.uid, er.start_time, er.submit_time, DATE_FORMAT(er.start_time, "%Y%m") AS start_month, DENSE_RANK() OVER (PARTITION BY er.uid ORDER BY DATE_FORMAT(er.start_time,"%Y%m") DESC) AS m_rank FROM exam_record er JOIN user_info ui USING (uid) WHERE er.uid IN ( SELECT uid FROM ( SELECT uid, ROW_NUMBER() OVER (ORDER BY 1 - COUNT(DISTINCT submit_time)/COUNT(DISTINCT start_time) DESC, uid) AS ir_rank FROM exam_record GROUP BY uid ) AS t1 JOIN( SELECT COUNT(DISTINCT uid) AS uid_cnt FROM exam_record ) AS t3 WHERE ir_rank <= CEILING(0.5*uid_cnt) ) AND ui.level IN (6, 7) ) t4 where t4.m_rank <= 3 GROUP BY t4.uid, t4.start_month ORDER BY uid