# 先找到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.主查询将其组配起来