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

with t as
(
select  all ui.uid,TAG,level,
        date_format(start_time,"%Y%m") sdt,
        date_format(submit_time,"%Y%m")edt,
        dense_rank() 
        over(partition by ui.uid order by date_format(start_time,"%Y%m") desc) as dt_rk
from user_info ui 
inner join exam_record er USING(uid) 
inner join examination_info USING(exam_id)

)
,T2 AS(
    SELECT UID
    FROM 
    (
     SELECT UID,incomplete_rate,CUME_DIST() OVER(ORDER BY incomplete_rate) AS RANK1,level
     #坑点3 这里的50%是排位不是未完成率
     FROM(
     select uid,level,
            sum(if(edt is null ,1,0)),count(sdt),AVG(if(edt is null ,1,0)) incomplete_rate   
     from t
     WHERE tag='SQL' #坑点2 这里筛选的未完成率是只对SQL考题的
     group by uid
     )T2T
    )T2T2
    WHERE RANK1>0.5 AND (level=6 or level=7) 
    #坑点4 排位必须是全体SQL类的排位，因此必须对SQL未完成率排序后再对等级进行筛选
)

select uid,sdt,
count(sdt),sum(if(edt is NOT null ,1,0))
from t #坑点1 最后的结果是针对所有类型的试卷，不单指SQL 
where  uid in 
(
  select * FROM T2
) AND dt_rk<=3
GROUP by uid,sdt
ORDER BY UID,sdt