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

select b.author_id
        ,t.author_level
        ,max(b.answer_num) as days_cnt
from
      (
        select a.author_id,date_add(a.dt,interval -rn day)
                ,count(*) as answer_num
        from
              (
              select author_id
                      ,answer_date as dt
                      ,ROW_NUMBER() over (partition by author_id order by answer_date asc) as rn
              from answer_tb ) a
        group by a.author_id,date_add(a.dt,interval -rn day))b
left join author_tb t
on b.author_id = t.author_id
group by b.author_id,t.author_level
having max(b.answer_num)>=3
order by b.author_id;