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

with t1 as (
    select a.author_id,
           answer_date,
           if(lag(date_add(answer_date, INTERVAL 2 DAY), 2) over (partition by a.author_id) = answer_date, 'Ture',
              'False') as result,
           author_level

    from answer_tb
             join author_tb a on answer_tb.author_id = a.author_id
    group by a.author_id, answer_date, author_level
    order by a.author_id, answer_date)
select author_id,
       author_level,
       count(*)+2 as 'days_cnt'
from t1
where result = 'Ture'
group by author_id,author_level;