题解 | #连续两次作答试卷的最大时间窗#
近三个月未完成试卷数为0的用户完成情况
http://www.nowcoder.com/practice/4a3acb02b34a4ecf9045cefbc05453fa
select
uid
,count(submit_time) exam_complete_cnt
from
(select uid
,submit_time
,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m')desc) r
from exam_record) r
where r<=3
group by uid
having count(submit_time)=count(uid)
uid
,count(submit_time) exam_complete_cnt
from
(select uid
,submit_time
,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m')desc) r
from exam_record) r
where r<=3
group by uid
having count(submit_time)=count(uid)
order by 2 desc,1 desc
这里要注意到为什么 date_format(start_time）而不是submit_time，因不能给空值排名，用having count(submit_time)=count(uid)巧妙的解决null值的问题。
查看16道真题和解析
