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

select 
t2.author_id
,author_level
,days_cnt
from (
        select
        author_id
        ,count(*) days_cnt
        from (
            select 
            author_id
            ,answer_date
            ,dense_rank()over(partition by author_id order by answer_date) num ---序号
            from answer_tb
            group by author_id,answer_date ---去重
             ) t1
        group by author_id,date_add(answer_date,interval -num day)---做差，分组
        having count(*)>=3
     ) t2
join author_tb t3
on t2.author_id=t3.author_id
order by t2.author_id