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

with a as(
    select
        distinct answer_date,
        author_id
    from 
        answer_tb 
),
b as (
    select
        answer_date,
        author_id,
        rank()over(partition by author_id order by answer_date) as rk
    from 
        a
),
c as (
    select 
        date_sub(answer_date,INTERVAL rk day) as new_dt,
        author_id
    from 
        b
),
d as (
    select 
        author_id,
        new_dt,
        count(*) as days
    from 
        c
    group by 
        author_id,
        new_dt
),
e as(
    select
        author_id,
        max(days) as days_cnt
    from 
        d
    group by 
        author_id
    having 
        days_cnt>=3
),
f as(
    select
        e.author_id,
        at.author_level,
        e.days_cnt
    from 
        e inner join author_tb as at on e.author_id = at.author_id
)
select * from f