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

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

http://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c

【问题拆解】

1、计算SQL试卷的完成率并按百分比排序； 2、查找出SQL试卷完成率较低的50%用户，且level为6级和7级的用户； 3、看他们所做过的试卷，每个月分别的答卷数目和完成数目，并将月份排序； 4、过滤出近三个月的数据，并按用户ID、月份升序排序。

【知识点】

date_format(datetime,'%Y%m')：将日期时间格式转化为‘年/月’格式
count()：计数函数
rank()over(partition by order by desc)：排序窗口函数，按分组并按降序排序
percent_rank() over(order by )：百分比排序窗口函数，按以百分比形式升序排序
子查询：嵌套查询，将查询结果用于下一个查询的条件

【分步实现】

1、计算SQL试卷的完成率并按百分比排序；

SELECT 
  uid,
  PERCENT_RANK()over( ORDER BY count(submit_time)/count(start_time))rate_rk
FROM 
  exam_record
WHERE
  exam_id in (select exam_id from examination_info where tag='SQL';

2、查找出SQL试卷完成率较低的50%用户，且level为6级和7级的用户；

  SELECT uid
  FROM
  	(SELECT uid,
    PERCENT_RANK()over( ORDER BY count(submit_time)/count(start_time))rate_rk
    FROM exam_record
    WHERE exam_id in (select exam_id from examination_info where tag='SQL')
    GROUP BY uid)a
  WHERE rate_rk<=0.5 
        AND uid IN (select uid from user_info where level in (6,7));

3、看他们所做过的试卷，每个月分别的答卷数目和完成数目，并将月份排序；

SELECT
	uid,
    date_format(start_time,'%Y%m')start_month,
    count(start_time)total_cnt,
    count(submit_time)complete_cnt,
    rank()over(partition by uid order by date_format(start_time,'%Y%m') desc)month_rank
FROM
    (SELECT uid
     FROM (SELECT uid,
            PERCENT_RANK()over( ORDER BY count(submit_time)/count(start_time))rate_rk
           FROM exam_record
           WHERE exam_id in (select exam_id from examination_info where tag='SQL')
           GROUP BY uid)a
     WHERE rate_rk<=0.5 
           AND uid IN (select uid from user_info where level in (6,7)))b
        LEFT JOIN exam_record USING(uid)
GROUP BY
     uid,start_month

4、过滤出近三个月的数据，并按用户ID、月份升序排序。

#完整答案
SELECT
    uid,start_month,total_cnt,complete_cnt
FROM
    (SELECT
        uid,
        date_format(start_time,'%Y%m')start_month,
        count(start_time)total_cnt,
        count(submit_time)complete_cnt,
        rank()over(partition by uid order by date_format(start_time,'%Y%m') desc)month_rank
    FROM
        (SELECT uid
        FROM
            (SELECT uid,
            PERCENT_RANK()over( ORDER BY count(submit_time)/count(start_time))rate_rk
            FROM exam_record
            WHERE exam_id in (select exam_id from examination_info where tag='SQL')
            GROUP BY uid)a
        WHERE rate_rk<=0.5 
            AND uid IN (select uid from user_info where level in (6,7)))b
        LEFT JOIN exam_record USING(uid)
    GROUP BY
        uid,start_month)c
WHERE
    month_rank<4
ORDER BY
    uid,start_month;

alt