题解 | 未完成率top50%用户近三个月答卷情况

SELECT
    uid,start_month,total_cnt,complete_cnt
FROM(
    SELECT
        uid,
        DATE_FORMAT(start_time, "%Y%m") start_month,
        COUNT(start_time) total_cnt,
        COUNT(submit_time) complete_cnt,
        ROW_NUMBER() OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time, "%Y%m") DESC) rk
    FROM
        user_info ui
    INNER JOIN
        exam_record er USING(uid)
    WHERE
        level IN (6,7) AND
        uid IN (
            SELECT
                uid
            FROM (
                SELECT
                    uid,
                    PERCENT_RANK() OVER(ORDER BY COUNT(submit_time)/COUNT(*)) prk
                FROM
                    exam_record er
                INNER JOIN
                    examination_info ei USING(exam_id)
                WHERE
                    tag = 'SQL'
                GROUP BY
                    uid
            )t1
            WHERE
                prk <= 0.5
        )
    GROUP BY    
        uid,start_month
)t2
WHERE
    rk <= 3
ORDER BY
    uid,start_month