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

我个人感觉这道题有点坑（可能是我语文不好，在钻牛角尖了）

先看题目要求：请统计SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序；行吧，一个一个来！

（1）统计SQL试卷上未完成率较高的50%的用户

先把完成率算出来看看是啥情况

SELECT 
    uid,
    COUNT(submit_time)/COUNT(start_time) rt_com  #计算完成率
FROM exam_record
WHERE exam_id IN(
    SELECT
        exam_id
    from
        examination_info 
    where tag = 'SQL')
GROUP BY uid

（这个结果竟然和题中给的结果不一样，题中直接筛选了近三个月的做题记录；我认为是先算用户的总完成率再根据月份进行下一步操作。）

OK，完成率出来了，怎么筛选出完成率较低的后50%用户呢，这时候就想要是有个百分比排名函数就好了（比如XX同学的GPA在5%这种功能），排名在开窗函数里就有，那就是今天的主角——percent_rank()

SELECT 
    uid,
    COUNT(submit_time)/COUNT(start_time) rt_com,
    percent_rank() over (order by  COUNT(submit_time)/COUNT(start_time)) as rk_com
	#这里为什么不用分区呢？是因为我们想要每个同学的排名，你再根据uid分区之后，第一名是你自己嘛
FROM exam_record
WHERE exam_id IN(
    SELECT
        exam_id
    from
        examination_info 
    where tag = 'SQL')
GROUP BY uid

哦！1002和1003是完成率低的50%用户

（2）完成率低的50%6级和7级用户是谁呢？

select 
    uid 
from(
    SELECT 
        uid,
        COUNT(submit_time)/COUNT(start_time) rt_com,
        percent_rank() over (order by  COUNT(submit_time)/COUNT(start_time)) as rk_com
    FROM exam_record
    WHERE exam_id IN(
        SELECT
            exam_id
        from
            examination_info 
        where tag = 'SQL')
    GROUP BY uid)as a
where
    uid in(
        select
            uid
        from
            user_info
        where level in (6,7)
    )
    and rk_com <= 0.5

（3）近三个月中，用户1002每个月的答卷数目和完成数目。按用户ID、月份升序排序

select
    uid,
    start_month,
    count(*) as total_cnt,
    count(score) as complete_cnt
from(
    select
        uid,
        date_format(start_time,'%Y%m') as start_month,
        score,
        dense_rank() over (partition by uid order by date_format(start_time,'%Y%m') desc)as rkm
    from
        exam_record
    )as t1
where rkm <= 3
group by uid,start_month

      
   select 
    *
from(
    select
        uid,
        start_month,
        count(*) as total_cnt,
        count(score) as complete_cnt
    from(
        select
            uid,
            date_format(start_time,'%Y%m') as start_month,
            score,
            dense_rank() over (partition by uid order by date_format(start_time,'%Y%m') desc)as rkm
        from
            exam_record
        )as t1
    where rkm <= 3
    group by uid,start_month)as t2 
	#这里是挑1002的代码嗷
where uid in(
     select 
        uid 
    from(
        SELECT 
            uid,
            COUNT(submit_time)/COUNT(start_time) rt_com,
            percent_rank() over (order by  COUNT(submit_time)/COUNT(start_time)) as rk_com
        FROM exam_record
        WHERE exam_id IN(
            SELECT
                exam_id
            from
                examination_info 
            where tag = 'SQL')
        GROUP BY uid)as a
    where
        uid in(
            select
                uid
            from
                user_info
            where level in (6,7)
        )
        and rk_com <= 0.5
)
order by uid,start_month