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

with
    temp as (
        select
            author_id,
            answer_date,
            rank() over (
                partition by
                    author_id
                order by
                    answer_date asc
            ) as rk,
            date_sub(
                answer_date,
                interval(
                    rank() over (
                        partition by
                            author_id
                        order by
                            answer_date asc
                    ) -1
                ) day
            ) as dd
        from
            (
                select distinct
                    author_id,
                    answer_date
                from
                    answer_tb
            ) as t1
        order by
            author_id,
            answer_date
    )
select
    author_id,
    author_level,
    days_cnt
from
    author_tb
    join (
        select
            author_id,
            dd,
            count(*) as days_cnt
        from
            temp
        group by
            author_id,
            dd
        having
            days_cnt >= 3
    ) as t2 using (author_id)