【牛客】SQL139 近三个月未完成试卷数为0的用户完成情况-窗口函数

描述

现有试卷作答记录表exam_record（uid:用户ID, exam_id:试卷ID, start_time:开始作答时间, submit_time:交卷时间，为空的话则代表未完成, score:得分）：

id	uid	exam_id	start_time	submit_time	score
1	1006	9003	2021-09-06 10:01:01	2021-09-06 10:21:02	84
2	1006	9001	2021-08-02 12:11:01	2021-08-02 12:31:01	89
3	1006	9002	2021-06-06 10:01:01	2021-06-06 10:21:01	81
4	1006	9002	2021-05-06 10:01:01	2021-05-06 10:21:01	81
5	1006	9001	2021-05-01 12:01:01	(NULL)	(NULL)
6	1001	9001	2021-09-05 10:31:01	2021-09-05 10:51:01	81
7	1001	9003	2021-08-01 09:01:01	2021-08-01 09:51:11	78
8	1001	9002	2021-07-01 09:01:01	2021-07-01 09:31:00	81
9	1001	9002	2021-07-01 12:01:01	2021-07-01 12:31:01	81
10	1001	9002	2021-07-01 12:01:01	(NULL)	(NULL)

找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名。由示例数据结果输出如下：

uid	exam_complete_cnt
1006	3

解释：用户1006近三个有作答试卷的月份为202109、202108、202106，作答试卷数为3，全部完成；用户1001近三个有作答试卷的月份为202109、202108、202107，作答试卷数为5，完成试卷数为4，因为有未完成试卷，故过滤掉。

select 
uid,count(act_time) as exam_complete_cnt
from
    (select
    uid,left(start_time,7) as act_time,submit_time,
    dense_rank() over(partition by uid order by left(start_time,7) desc) as rnk
    from
    exam_record)t1
where rnk<=3
group by uid
having count(act_time)=sum(if(submit_time is null,0,1))
order by exam_complete_cnt desc,uid desc