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

select
    uid,
    date_format (start_time, '%Y%m') as start_month,
    count(start_time) as total_cnt,
    count(score) as complete_cnt
from
    (
        select
            uid,
            start_time,
            score,
            DENSE_RANK() OVER (
                PARTITION BY
                    uid
                ORDER BY
                    date_format (start_time, '%Y%m') desc
            ) as rk
        from
            exam_record
    ) as recent_months
where
    rk <= 3
    and uid in (
        select
            uid
        from
            user_info as ui
        where
            level = 6
            or level = 7
    )
    and uid in (
        SELECT
            uid
        FROM
            (
                SELECT
                    er.uid,
                    COUNT(submit_time) / COUNT(start_time) AS incomplete_rate,
                    NTILE (2) OVER (
                        ORDER BY
                            COUNT(submit_time) / COUNT(start_time) asc
                    ) AS rate_rank
                FROM
                    exam_record AS er
                    LEFT JOIN examination_info AS ei ON er.exam_id = ei.exam_id
                WHERE
                    ei.tag = 'SQL'
                GROUP BY
                    er.uid
            ) AS RankedUsers
        WHERE
            rate_rank = 1
    )
group by
    uid,
    start_month
order by
    uid,
    start_month
	
	虽然完成了 但是知道写得有点啰嗦 不够简洁美丽