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

#先统计每个人的SQL试卷上未完成率前50%的用户，再进行用户等级检索，再根据要求筛选数据
select
uid
,substring(replace(start_time,'-',''),1,6) start_month
,count(*) total_cnt
,count(submit_time) complete_cnt
from
(
    select
    uid
    ,start_time
    ,submit_time
    ,dense_rank()over(partition by uid order by substring(start_time,1,7) desc) rk#不跳过排名存在并列情况
    from exam_record
    where uid in
    (
        select
        uid
        from
        (
            select
            er.uid uid
            ,level
            ,percent_rank()over(order by count(submit_time)/count(start_time)) rk2#完成率排名后50%，并不是完成率小于50%。排名是与其他用户的比率进行比较。
            from exam_record er left join user_info ui
            on er.uid = ui.uid
            left join examination_info ei
            on er.exam_id = ei.exam_id
            where tag = 'SQL'
            group by 1
        ) table1
        where rk2 <= 0.5#未完成率较高,完成率小于等于0.5
        and level in (6,7)
    ) 
) table2   
where rk < 4
group by 1,2
order by 1,2