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

如何求连续打卡，以及连续签到得金币等问题就是用打卡日期减去该日期的排名，相减相同的为一个连续打卡的分组，然后记数即可

select id.author_id,author_level,cnt
from (
SELECT author_id,first_day,count(distinct answer_date) as cnt
from
(SELECT DENSE_RANK() over(partition by author_id 
                             order by answer_date) as rk,
    TIMESTAMPADD(day,-DENSE_RANK() over(partition by author_id 
                             order by answer_date)+1,answer_date) AS first_day,
    author_id,answer_date
    from answer_tb) base
group by author_id,first_day
having cnt>=3) id left join author_tb using(author_id)