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

WITH t as(
SELECT 
exam_record.uid,
SUM(IF(submit_time IS NULL,1,0)) AS incomplete_cnt,
COUNT(start_time) AS total_cnt,	
SUM(IF(submit_time IS NULL,1,0)) / COUNT(start_time) AS incomplete_rate,
PERCENT_RANK()OVER(ORDER BY SUM(IF(submit_time IS NULL,1,0)) / COUNT(start_time) DESC) AS p_ranking
FROM examination_info
JOIN exam_record ON examination_info.exam_id = exam_record.exam_id
WHERE tag = 'SQL'
GROUP BY exam_record.uid
),
t1 as (
SELECT t.uid
FROM t
JOIN user_info 
ON t.uid = user_info.uid
WHERE P_ranking <= 0.5 AND(level = 6 OR level = 7)
),
t2 as(
SELECT t1.uid,
DATE_FORMAT(start_time,'%Y%m') AS start_month,
DENSE_RANK()OVER(PARTITION BY t1.uid ORDER BY DATE_FORMAT(start_time,'%Y%m') DESC) AS m_ranking,
COUNT(start_time) AS total_cnt,
COUNT(submit_time) AS complete_cnt
FROM t1
JOIN exam_record ON t1.uid = exam_record.uid  
GROUP BY t1.uid,DATE_FORMAT(start_time,'%Y%m')   
)
SELECT uid,start_month,total_cnt,complete_cnt
FROM t2
WHERE m_ranking <= 3
ORDER BY uid,start_month;