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

with tmp as(
    select author_id, answer_date, row_number() over(partition by author_id order by  answer_date) as r from (select author_id, answer_date from answer_tb
    group by author_id, answer_date
    ) a
),
 dt as(
    select author_id, count(*) as cnt from (select author_id, date_sub(answer_date, interval r day) as bas from tmp) b
    group by author_id, bas
)
select author_id, author_level, max(cnt) from dt  left join author_tb using(author_id)
group by author_id, author_level
having max(cnt) >= 3
order by author_id asc

经典模板题目，我把它叫做bas而不是dt