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

select t3.author_id
        ,author_level
        ,days_cnt
from author_tb
inner join(
    select author_id
            ,max(rk) AS days_cnt
    from(
        select author_id
                ,row_number() over(partition by author_id,date_sub(answer_date,interval rn day)) AS rk
        from(
            select author_id
                    ,answer_date
                    ,row_number() over(partition by author_id order by answer_date) AS rn
            from answer_tb
            #group by author_id,answer_date是为了防止重复计算一个用户一天登录多次
            group by author_id
                    ,answer_date
        )t1
    )t2
    group by author_id
    having max(rk)>=3
)t3
on author_tb.author_id = t3.author_id
order by author_id

1. t1的rn排序值表明用户共回答了多少天，group by保证了去重，也就是一天内回答多次
2. 对回答日期answer_date减去rn即可得到差值，差值相同则为连续，对差值date_sub(answer_date, interval rn day)做开窗排序，按照用户区分，同个差值的分为同组，因为row_number()是不去重的，所以最大值(rk)也就是差值相同的最大个数