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

with t as(
    select author_id, DATE_SUB(answer_date, INTERVAL rk Day) 'continue_day'
    from(
        select answer_date, author_id,row_number()over(PARTITION by author_id
        Order by answer_date asc) 'rk'
        from answer_tb
    )a
)

select a.author_id, author_level, days_cnt
from 
(
    select author_id, max(days_cnt) 'days_cnt'
    from
    (
        select author_id, count(*) 'days_cnt'
        from t
        group by author_id,continue_day
        having days_cnt >= 3
    )b
    group by author_id
)c
join author_tb a on a.author_id = c.author_id

• 前置知识：
• 如果是连续登录，那么当前日期answer_date - 对应的row_number得到的日期是一致的
• 
• 首先通过count(continue_day)，筛选出最大连续天数大于3天的用户,再通过max求出其最大连续天数，最后拼接author_tb表获取用户level即可