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

select uid,count(score) exam_complete_cnt
from

(select uid,start_time,score,dense_rank()over(partition by uid order by date_format(start_time,"%y%m") desc ) ranking
from exam_record
) t1
where ranking<=3 
group by uid
having count(score)=count(uid)
order by exam_complete_cnt desc,uid desc  

首先筛选出每名用户近三个月的答题明细，使用窗口函数按照月份降序排名

这边需要dense_rank因为相同月份出现应该算在同一排名 date_format提取年月

然后查询条件 名词要小于等于3

按uid分组

分组后过滤条件：score不存在空值

聚合函数 count