通过代码

select
    uid,
    mon start_month,
    count(*) total_cnt,
    count(score) cmoplete_cnt
from (SELECT
          u_i.uid,
          start_time,
          submit_time,
          score,
          DATE_FORMAT(start_time,'%Y%m') mon,
          DENSE_RANK() over(
              partition by   uid
              order by  DATE_FORMAT(start_time,'%Y%m') desc
              )  ranking
      FROM
          (SELECT
               uid,
               percent_rank()over(
                   order by (count(*) - COUNT(score)) /  count(*) desc
                   ) rate0
           FROM
               exam_record
                   RIGHT JOIN
               examination_info
               ON
                       exam_record.exam_id = examination_info.exam_id
           WHERE
                   tag = 'SQL'
           GROUP BY
               uid) pa
              RIGHT JOIN user_info u_i
                         on u_i.uid = pa.uid
              left join exam_record e_r
                        on e_r.uid = u_i.uid
      where
              level>5 and
              rate0 <=0.5
     )pai
where ranking<4
group by uid,mon
order by uid,mon;

思路

这道题，我原称之为究极套娃！！！！！真的是那啥带那啥一套又一套、

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

1.sql试卷未完成率较高的50%用户

2.有试卷作答记录的6 .7 级近三个月

3.答卷数、完成数、排序

1.sql试卷未完成率较高的用户

SELECT
 uid,
 percent_rank()over(
     order by (count(*) - COUNT(score)) /  count(*) desc
     ) rate0
FROM
 exam_record
     RIGHT JOIN
 examination_info
 ON
         exam_record.exam_id = examination_info.exam_id
WHERE
     tag = 'SQL'
GROUP BY
  uid

上上一篇有percent_rank就不说了，那么问题是：不是较高的50%吗？

是因为窗口函数写在select里，不能用where或者having只能进行一个子查询，所以

2.有试卷作答记录的6 .7 级近三个月

SELECT
  u_i.uid,
  score,
  DATE_FORMAT(start_time,'%Y%m') mon,
  DENSE_RANK() over(
      partition by   uid
      order by  DATE_FORMAT(start_time,'%Y%m') desc
      )  ranking
FROM
  (1.代码) pa
      RIGHT JOIN user_info u_i
                 on u_i.uid = pa.uid
      left join exam_record e_r
                on e_r.uid = u_i.uid
where
      level>5 and
      rate0 <=0.5

啊，刚做过近三个月也就是order by DATE_FORMAT(start_time,'%Y%m') desc，然后再进行子查询<4 筛选出来就行

在这一层进行 %50操作 ，顺便一个表连接把6 7 级搞定（因为最高就是7级所以这里可用>5） 再顺便吧最后一层要用的东西查出来。

3.答卷数、完成数

select
    uid,
    mon start_month,
    count(*) total_cnt,
    count(score) cmoplete_cnt
from (2.代码)pai
where ranking<4
group by uid,mon
order by uid,mon;

count会忽略掉null所以直接count(score)就好