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

select
    b1.author_id,
    b2.author_level,
    b1.days_cnt
from (
    select 
        author_id,
        count(author_id) days_cnt
    from (
        select
            author_id,
            answer_date,
            dr,
            DATE_SUB(answer_date, INTERVAL dr DAY) date1
        from (
            select
                answer_date,
                author_id,
                dense_rank() over(partition by answer_date) dr
            from answer_tb
            group by answer_date,author_id
        ) t
    ) a
    where DATE_SUB(answer_date, INTERVAL dr DAY) = date1
    group by author_id
    having days_cnt >= 3 
) b1
inner join author_tb b2
on b1.author_id = b2.author_id