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

# 先找到sql试卷未完成率较高的前一半用户，筛选出level是6、7的
# 这些用户的有作答记录的近三个月，分组统计每月的答卷数和完成数

WITH temp AS
    ( # 联结表
    SELECT a.uid, c.start_time, c.score, a.level, b.tag
    FROM exam_record c
        LEFT JOIN user_info a USING(uid)
        LEFT JOIN examination_info b USING(exam_id)
    # WHERE b.tag = 'SQL' #AND a.level IN (6,7)
    ),
    # 找到较高的50%的用户，还是要排序在前50%
    50_temp AS
    (
    SELECT uid, ROUND(SUM(IF(score IS NULL, 1, 0))/COUNT(start_time), 4) incomplete_rate
    FROM temp
    WHERE tag = 'SQL'
    GROUP BY uid
    ),
    u_temp AS
    (
    SELECT uid, PERCENT_RANK() OVER(ORDER BY incomplete_rate) rate_rank
    FROM 50_temp
    )




SELECT uid, DATE_FORMAT(start_time, "%Y%m") start_month,
        COUNT(start_time) total_cnt, 
        COUNT(score) complete_cnt
FROM (
    SELECT uid, start_time, score, DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time, "%Y%m") DESC) rank_month # 按时间对记录排序
    FROM temp
    WHERE uid IN ( # uid的确定
        SELECT uid 
        FROM u_temp
        WHERE rate_rank >= 0.5 # 未完成率在前50%
        ) AND level IN (6,7)
    ) b
WHERE rank_month < 4 # 近三个月限定
GROUP BY uid, start_month # 按照这两个字段分组
ORDER BY uid, start_month

题目拆解：1.SQL试卷上未完成率较高的50%用户中——限定了用户范围，是在sql上未完成率排序前50%的用户（最开始看示例，我还以为是未完成率高于50%的人）

2.6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目——跟上一题相似

新知识点：

1.percent_rank() over() 按照数字所在的位置进行百分位分段

2.ntile(n)over() 将数字按照大小平均分成n段

3.在标准的SQL中，一个查询只能有一个WITH子句，但是可以在单个WITH子句中定义多个公用表表达式（CTE）。每个CTE之间用逗号分隔。

4.dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

踩的坑：

1.看示例以为是按未完成率的大小筛选，但是根据题意还是要排序

2.题意是限制了用户范围，但是没有限制作答记录的题目类型，最开始我给限制了，老是少一条记录

思路：

1.主查询用来查询结果示例的三个字段。需要满足的条件是用户是在一定范围内的；用户等级是由限制的；作答记录的日期是由限制的

2.根据上述限制，我们要先把用户，特别是SQL试卷上未完成率较高的50%用户中 找出来，然后再同时找到符合等级的用户。

3.用户确定了，再找到每个用户近三个月的作答记录，再统计总作答和实际完成数，最后排序。

做法：

1.将三个表有用的字段联结起来，内联结或左连接都可以，创建了第一个临时表

2.SQL试卷上未完成率较高的50%用户中 ：根据SQL题初步筛选用户，并且计算未完成率，使用if函数，创建第二个临时表；创建第三个，来对未完成率进行百分比排序，使用pertcent_rank，我先使用正序排列未完成率，这个时候未完成率小的排前面的，所以在主查询采用>=

3.主查询将其组配起来