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

select
    d.author_id,
    e.author_level,
    d.days_cnt
from
    (
        select
            author_id,
            count(*) as days_cnt
        from
            (
                select
                    author_id,
                    answer_date - a zz
                from
                    (
                        select
                            author_id,
                            answer_date,
                            dense_rank() over (
                                partition by
                                    author_id
                                order by
                                    answer_date
                            ) a
                        from
                            answer_tb
                    ) b
                group by
                    author_id,
                    answer_date,
                    a
            ) c
        group by
            author_id,
            zz
        having
            days_cnt >= 3
        order by
            author_id asc
    ) d
    left join author_tb e on d.author_id = e.author_id;