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

题目

请统计SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序。

步骤：

1、SQL试卷上未完成率较高的50%用户——计算未完成率，然后使用ntile() over(未完成率降序) 窗口函数，创建子查询，选择ranking=1且是6/7级用户

select uid
        from(
              select uid,level,
                   -- sum(if(submit_time is null,1,0)) incomplete_cnt,
                   -- count(start_time) total_cnt,
                   -- round(sum(if(submit_time is null,1,0))/count(start_time),4) incomplete_rate,
             		ntile(2) over(order by (sum(if(submit_time is null,1,0))/count(start_time)) desc) as incomplete_ranking
              from exam_record join user_info using(uid)
                              join examination_info using(exam_id)
              where tag='SQL'
              group by uid)
 where a.incomplete_ranking=1 and level in (6,7)

2、有试卷作答记录的近三个月

select *
from(
    select uid,start_time,submit_time,
        dense_rank() over(partition by uid order by left(start_time,7) desc) as time_ranking
        from exam_record) rk
where rk.time_ranking<=3

3、每个月的答卷数目和完成数目 group by uid,start_month

select uid,
    DATE_FORMAT(start_time,'%Y%m') start_month,
    count(start_time) total_cnt,
    count(submit_time) complete_cnt
from(
    select uid,start_time,submit_time,
    dense_rank() over(partition by uid order by left(start_time,7) desc) as time_ranking
    from exam_record) rk
where rk.time_ranking<=3 and uid in(
        select uid
        from (select uid, 
                        level,
                        -- sum(if(submit_time is null,1,0)) incomplete_cnt,
                        -- count(start_time) total_cnt,
                        -- round(sum(if(submit_time is null,1,0))/count(start_time),4) incomplete_rate,
                        ntile(2) over(order by (sum(if(submit_time is null,1,0))/count(start_time)) desc) as incomplete_ranking
                    from exam_record join user_info using(uid)
                    join examination_info using(exam_id)
                    where tag='SQL'
                    group by uid) a
            where a.incomplete_ranking=1 and level in (6,7)
            ) 
group by uid,start_month
order by uid,start_month