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

with t1 as (
select answer_date,
author_id,
row_number() over (partition by author_id order by answer_date) as ranks
from answer_tb)

select t3.author_id,
author_level,
log_cnt
from(
select author_id,
gap,
count(answer_date) as log_cnt
from
(
select answer_date,
author_id,
date_sub(answer_date, interval ranks day) as gap
from t1
)t2
group by author_id,
gap
)t3
left join author_tb at
on t3.author_id=at.author_id
where log_cnt>=3