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

with t1 as (
    select
        answer_date,
        author_id
    from 
        answer_tb
    group by 
        answer_date,
        author_id
)

select
    t5.author_id,
    author_tb.author_level,
    t5.days_cnt
from (
    select
        author_id,
        max(answer_cnt) as days_cnt
    from (
        select
            author_id,
            dt_diff,
            count(dt_diff) as answer_cnt
        from (
            select
                author_id,
                answer_date,
                rn,
                date_sub(answer_date,interval rn day) as dt_diff
            from (
                select
                    author_id,
                    answer_date,
                    row_number() over (partition by author_id order by answer_date) as rn
                from 
                    t1
            ) as t2
        ) as t3
        group by 
            author_id,
            dt_diff
    ) as t4
    group by 
        author_id
) as t5
left join 
    author_tb
on 
    t5.author_id = author_tb.author_id
where 
    t5.days_cnt >= 3
order by 
    t5.author_id