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

select
    uid,
    start_month,
    total_cnt,
    complete_cnt
from
    (
        select
            uid,
            date_format (start_time, '%Y%m') start_month,
            rank() over (
                partition by
                    uid
                order by
                    date_format (start_time, '%Y%m') desc
            ) 排序,
            count(date_format (start_time, '%Y%m')) total_cnt,
            sum(
                case
                    when submit_time is not null then 1
                    else 0
                end
            ) complete_cnt
        from
            test.exam_record
        where
            uid in (
                select
                    a.uid
                from
                    (
                        select
                            uid,
                            sum(
                                case
                                    when submit_time is null then 1
                                    else 0
                                end
                            ) incomplete_cnt,
                            count(start_time) total_cnt,
                            sum(
                                case
                                    when submit_time is null then 1
                                    else 0
                                end
                            ) / count(start_time) incomplete_rate,
                            percent_rank() over (
                                order by
                                    sum(
                                        case
                                            when submit_time is null then 1
                                            else 0
                                        end
                                    ) / count(start_time) desc
                            ) 百分比排序
                        from
                            test.exam_record er
                            join test.examination_info ei on er.exam_id = ei.exam_id
                        where
                            tag = 'SQL'
                        group by
                            uid
                    ) a
                    join test.user_info ui on a.uid = ui.uid
                where
                    level between 6 and 7
                    and 百分比排序 <= 0.5
            )
        group by
            uid,
            date_format (start_time, '%Y%m')
    ) b
where
    b.排序 <= 3
order by
    uid asc,
    start_month asc