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

#根据回答日期排序并赋予排序值，日期减去排序值相同的，即为连续回答
with a as (select count(*) as cnt,t1.author_id
from
(select author_id,answer_date,row_number()over(partition by author_id order by answer_date) as ranking
from answer_tb
)t1
group by author_id,answer_date-ranking
)
#根据虚拟表筛选最大连续回答，并增加having条件
select t2.author_id,author_level,days_cnt
from(
    select max(cnt) as days_cnt,author_id
    from a
    group by author_id
) t2
left join author_tb a
on a.author_id=t2.author_id
having days_cnt>=3