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

SELECT uid, start_month
        ,COUNT(start_month) AS total_cnt
        ,COUNT(submit_time) AS complete_cnt
FROM (
    SELECT uid, exam_id, level, submit_time
            , 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 mon_level
    FROM user_info
    JOIN exam_record USING(uid)
    JOIN examination_info USING(exam_id)
    WHERE uid in (
        SELECT uid
        FROM (
            SELECT uid, PERCENT_RANK() OVER(ORDER BY COUNT(submit_time) / COUNT(start_time)) AS rate
            FROM user_info
            JOIN exam_record USING(uid)
            JOIN examination_info USING(exam_id)
            WHERE tag = "SQL" 
            GROUP BY uid
        ) data1
        WHERE rate <= 0.5 
    )  AND level IN (6, 7)
) data

WHERE mon_level <= 3
GROUP BY uid,start_month
ORDER BY  uid, start_month