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

这个题说真的，他的描述是有问题的，特别是这一句“此较高的50%用户（排位<=0.5）” 我是真服气了，这句话的括号里面的是一句屁话，我也不知道出题人脑袋热还是咋地。

步骤见注释部分。

我是按照一步一步逻辑写出来的，就是我写的注释

不想优化。看着就烦

#基础分析
    #先找出符合条件的用户
    #examination_info.tag = SQL,
    #计算出每个用户的未完成率(用户的未完成数/用户答题总数)，取出 前百分之50的未完成用户
    #同时找出这百分之50的用户中，6级或7级的用户

    #找出符合条件用户之后的操作
    #前面三个条件过滤出来的用户，分别找出每个用户 的近三个月的答卷记录，再统计出用户每一月的答卷数目和完成数目
    #最后排序
select
    j.uid,j.start_month,j.total_cnt,j.complete_cnt
from 
(
    select
        h.uid,h.start_month,h.startNum as total_cnt,h.submitNum as complete_cnt,
        row_number() over(partition by h.uid order by h.start_month desc) as rk
    from 
    (
        select
            g.uid,date_format(g.start_time,"%Y%m") as start_month,
            sum(case when g.submit_time is not null then 1 else 0 end) as submitNum,
            count(1) as startNum
        from
        (
            select
                d.uid
            from 
            (
                select
                    c.uid, row_number() over (order by c.incomplete_rate  desc) as rk,sum(1) over() as nums
                from 
                (
                    select
                        a.uid,sum(case when a.submit_time is null then 1 else 0 end)/count(1) as incomplete_rate
                    from exam_record a join examination_info b on a.exam_id = b.exam_id and b.tag="SQL"
                    group by a.uid 
                ) c 
            ) d join user_info e on d.uid = e.uid and (e.level=7 or e.level = 6)
            where d.rk <= ceil(nums*0.5)

        ) f join exam_record g on f.uid = g.uid

        group by g.uid,date_format(g.start_time,"%Y%m")

    ) h
) j where j.rk <= 3
order by j.uid,j.start_month