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

with t1 as(
    select distinct author_id,author_level,answer_date,
    subdate(answer_date, interval dense_rank() over(partition by author_id order by answer_date) day) as tmp_dt
    from answer_tb join author_tb using(author_id)
),
t2 as(
    select author_id,author_level,
    count(tmp_dt) as cnt
    from t1
    group by author_id,author_level,tmp_dt
)
select author_id,author_level,
max(cnt) as days_cnt
from t2
group by author_id,author_level
having days_cnt>=3
order by author_id asc