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

题目

表：试卷作答记录表

问题：

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

思路

1. 找到每个人近三个有试卷作答记录的月份： 用窗口函数dense_rank给start_time的年月排序（注意是降序）,然后嵌套查询，where筛选ranking<=3的行，这些就是用户近三个月作答的所有试卷。
2. 没有试卷是未完成状态： 所有试卷都是已完成，即限定条件count(start_time)=count(submit_time)，但要注意，这个是用uid分组之后对组筛选的条件，所以用having

 select uid, count(submit_time) as exam_complete_cnt
 from
(select uid,  date_format(start_time,'%Y%m') tt, start_time,submit_time,
dense_rank() over 
(partition by uid order by date_format(start_time,'%y%m')) desc) rk
from exam_record) a 
where rk<=3 
group by uid 
having count(submit_time)= count(start_time)
order by exam_complete_cnt desc,uid desc

一开始的思路：

找到近三个月的月份，然后distinct一下，再限制一下用户试卷start_time的范围在这三个月里，就是说绕了一大圈

with b AS(
SELECT distinct a.tt,a.uid,a.submit_time
    from
        (select uid,
                 submit_time,
                 date_format(start_time,'%Y%m') as tt,
                  dense_rank()
                  over(partition by uid
                      order by date_format(start_time,'%y%m')) as ranking
        from exam_record
         ) a
     where ranking<=3) 

select er.uid,count(er.submit_time) as exam_complete_cnt
from exam_record er join b using(uid)
where date_format(er.start_time,'%Y%m') between min(b.tt) and max(b.tt)
group by er.uid
*/