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

/*筛选出SQL完成率后50%的6级或7级用户*/
with t3 AS(
    select t2.uid
    from(
        select uid
                ,percent_rank() over(order by round(count(submit_time)/count(start_time),1) asc) AS incomplete_rate_rank
        from exam_record
        where exam_id in(
            select exam_id from examination_info where tag = 'SQL'
        )
        group by uid
    )t2
    inner join user_info ui on t2.uid = ui.uid
    where incomplete_rate_rank <= 0.5 and (level = 6 or level = 7)
)

select uid
        ,start_month
        ,total_cnt
        ,complete_cnt
from(
    select t3.uid
            ,date_format(start_time,'%Y%m') AS 'start_month'
            ,count(start_time) AS total_cnt
            ,count(submit_time) AS complete_cnt
            ,dense_rank() over(partition by t3.uid order by date_format(start_time,'%Y%m') desc) AS month_rank
    from t3 
    left join exam_record er on t3.uid = er.uid
    group by t3.uid
            ,date_format(start_time,'%Y%m') /*按照月份和uid统计每个月的试卷作答情况*/
    )t4 /*t4：每个用户每个月的SQL试卷作答情况，并且给月份排名*/
where month_rank <= 3
order by uid asc
        ,start_month asc