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

#建立临时表查询SQL试卷上未完成率较高的50%用户
with t1 as #用户表 
(select
a.uid
from
    (select
    uid
    ,percent_rank()over(order by(sum(if(score is null,1,0))/count(er.exam_id)))
    as pk #percent_rank窗口函数
    from 
    exam_record er
    left join examination_info ei
    on er.exam_id=ei.exam_id
    where tag='SQL' #sql条件
    group by 1)a
left join user_info ui
on a.uid=ui.uid
where level between 6 and 7 #等级条件
and pk>=0.5)  #50%

,t2 as  #时间表
(select
uid
,exam_id
,date_format(start_time,'%Y%m') as start_month
,score
from exam_record
order by start_month)

#连接用户表和时间表
select 
uid
,start_month
,count(exam_id) as total_cnt
,sum(if(score is not null,1,0)) as complete_cnt
from 
    (select       #时间格式202002排序
    t1.uid as uid
    ,start_month
    ,dense_rank()over(partition by uid order by start_month desc)as dr
    ,exam_id
    ,score
    from
    t2
    inner join t1
    on t2.uid=t1.uid)tmp
where dr<=3  #时间筛选条件
group by 1,2
order by 1,2;