题解 | #最大连续回答问题天数大于等于3天的用户及其等级#

with t1 as(
    select author_id,answer_date, rank()over(partition by author_id order by answer_date) rt
    from (
        select distinct author_id,answer_date
        from  answer_tb 
    )t
)
select author_id,author_level,max(cnt)
from
( 
    select author_id,count(*)cnt
    from t1
    group by  author_id,answer_date-rt
) t2 join author_tb at using (author_id)
group by author_id,author_level
having max(cnt)>=3
order by author_id