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

select 
    au.author_id author_id
    ,au.author_level
    ,t1.cnt days_cnt
from
(
select 
    id
    ,k
    ,count(dt) cnt
from 
(select 
    distinct author_id id
    ,answer_date dt
    ,dense_rank()over(partition by author_id order by answer_date) r 
    ,answer_date-dense_rank()over(partition by author_id order by answer_date) k
from 
    answer_tb 
 ) t0
group by 
    id 
    ,k 
having
    count(dt)>=3 
) t1
left join 
    author_tb au 
on 
    au.author_id=t1.id
order by 
    author_id