题解 | #某乎问答最大连续回答问题用户及其对应等级#

select t.author_id, b.author_level, t.days_cnt
from (
    select a.author_id, count(1) as days_cnt
    from (
        select distinct author_id, answer_date, 
        dense_rank() over(partition by author_id order by answer_date) as ranks
        from answer_tb
    ) a
    group by author_id, 
    date_sub(a.answer_date, interval ranks day)
    having count(1) >= 3
    ) t
left join author_tb b 
on t.author_id = b.author_id
order by b.author_id asc