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

with t1 as
(select 
    distinct author_id,
    answer_date,
    dense_rank() over(partition by author_id order by answer_date) rn
from answer_tb),
t2 as 
(select 
    author_id,
    count(1) as day_cnt
from t1 
group by author_id, date_sub(answer_date, interval rn day)
having count(1) >= 3)
select 
    t2.author_id,
    author_level,
    day_cnt
from t2 
join author_tb using(author_id)
order by author_id