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

with a as(select distinct author_id,answer_date from answer_tb)
select c.author_id,author_level,max(days_cnt) days_cnt from
(select author_id,sub,count(*) days_cnt from
(select *,
row_number() over(partition by author_id order by answer_date) rownumber,
answer_date-row_number() over(partition by author_id order by answer_date) sub
from a
)b
group by author_id,sub
)c
left join author_tb at on c.author_id=at.author_id
group by c.author_id,author_level
having days_cnt>=3