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

with data as
(select distinct
b.author_id,b.answer_date,
dense_rank() over(
    partition by b.author_id
    order by b.answer_date
) as rn
from 
author_tb as a
right join answer_tb as b on  a.author_id=b.author_id
group by b.author_id,b.answer_date),

 data2 as (
select 
author_id,count(*) as r
from
data
group by
author_id,
date_sub(answer_date,interval rn day)
having count(*)>=3)


select 
data2.author_id,

author_tb.author_level,r

from data2 
left join author_tb on data2.author_id=author_tb.author_id