题解 | #近三个月未完成试卷数为0的用户完成情况#

近三个月未完成试卷数为0的用户完成情况

http://www.nowcoder.com/practice/4a3acb02b34a4ecf9045cefbc05453fa

明确题意：

找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名

问题拆解：

本题主要是考察知识点：dense_rank、group by、date_format等
DATE_FORMAT(submit_time, "%Y-%m-%d" ) 返回的是2021-09-01这样；dense_rank遇到相同的排序是1 ,2,2,3这样；
先查出最近3个月的作答记录，记得使用dense_rank排序！！
在having 中筛选，在select中用case when判断计数

代码实现：

select 
uid ,
count(case when score is not null then 1 else null end) as exam_complete_cnt -- 作答完成的次数
from (
select * ,
dense_rank() over(partition by uid order by date_format(start_time,'%Y-%m') desc) as rn -- 按照月份排序
from  exam_record 
   )t1 where rn <= 3 -- 最近三个月的，月份不一定要连续的，9，6，5也是最近3个月！
group by uid 
having count(case when score is null then 1 else null end) = 0 -- 无未作答的
order by exam_complete_cnt desc ,uid desc;

不足之处，欢迎指正

中间过程：

mysql> select * ,
    -> dense_rank() over(partition by uid order by date_format(start_time,'%Y-%m') desc) as rn
    -> from  exam_record 
    -> 
    -> 
    -> 
    -> ;
+----+------+---------+---------------------+---------------------+-------+----+
| id | uid  | exam_id | start_time          | submit_time         | score | rn |
+----+------+---------+---------------------+---------------------+-------+----+
|  6 | 1001 |    9001 | 2021-09-05 10:31:01 | 2021-09-05 10:51:01 |    81 |  1 |
|  7 | 1001 |    9003 | 2021-08-01 09:01:01 | 2021-08-01 09:51:11 |    78 |  2 |
|  8 | 1001 |    9002 | 2021-07-01 09:01:01 | 2021-07-01 09:31:00 |    81 |  3 |
|  9 | 1001 |    9002 | 2021-07-01 12:01:01 | 2021-07-01 12:31:01 |    81 |  3 |
| 10 | 1001 |    9002 | 2021-07-01 12:01:01 | NULL                |  NULL |  3 |
|  1 | 1006 |    9003 | 2021-09-06 10:01:01 | 2021-09-06 10:21:02 |    84 |  1 |
|  2 | 1006 |    9001 | 2021-08-02 12:11:01 | 2021-08-02 12:31:01 |    89 |  2 |
|  3 | 1006 |    9002 | 2021-06-06 10:01:01 | 2021-06-06 10:21:01 |    81 |  3 |
|  4 | 1006 |    9002 | 2021-05-06 10:01:01 | 2021-05-06 10:21:01 |    81 |  4 |
|  5 | 1006 |    9001 | 2021-05-01 12:01:01 | NULL                |  NULL |  4 |
+----+------+---------+---------------------+---------------------+-------+----+
10 rows in set (0.00 sec)

mysql> 
mysql> select * from (
    -> select * ,
    -> dense_rank() over(partition by uid order by date_format(start_time,'%Y-%m') desc) as rn
    -> from  exam_record 
    ->    )t1 where rn <= 3 
    -> ;
+----+------+---------+---------------------+---------------------+-------+----+
| id | uid  | exam_id | start_time          | submit_time         | score | rn |
+----+------+---------+---------------------+---------------------+-------+----+
|  6 | 1001 |    9001 | 2021-09-05 10:31:01 | 2021-09-05 10:51:01 |    81 |  1 |
|  7 | 1001 |    9003 | 2021-08-01 09:01:01 | 2021-08-01 09:51:11 |    78 |  2 |
|  8 | 1001 |    9002 | 2021-07-01 09:01:01 | 2021-07-01 09:31:00 |    81 |  3 |
|  9 | 1001 |    9002 | 2021-07-01 12:01:01 | 2021-07-01 12:31:01 |    81 |  3 |
| 10 | 1001 |    9002 | 2021-07-01 12:01:01 | NULL                |  NULL |  3 |
|  1 | 1006 |    9003 | 2021-09-06 10:01:01 | 2021-09-06 10:21:02 |    84 |  1 |
|  2 | 1006 |    9001 | 2021-08-02 12:11:01 | 2021-08-02 12:31:01 |    89 |  2 |
|  3 | 1006 |    9002 | 2021-06-06 10:01:01 | 2021-06-06 10:21:01 |    81 |  3 |
+----+------+---------+---------------------+---------------------+-------+----+
8 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> select 
    -> uid ,
    -> count(case when score is not null then 1 else null end) as exam_complete_cnt
    -> from (
    -> select * ,
    -> dense_rank() over(partition by uid order by date_format(start_time,'%Y-%m') desc) as rn
    -> from  exam_record 
    ->    )t1 where rn <= 3 
    -> group by uid 
    -> having count(case when score is null then 1 else null end) = 0 ;
+------+-------------------+
| uid  | exam_complete_cnt |
+------+-------------------+
| 1006 |                 3 |
+------+-------------------+
1 row in set (0.00 sec)

mysql>