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

select c.author_id,c.author_level,count(*) from 
(select 
b.author_id,
b.answer_date,
b.author_level,
date_sub(b.answer_date,INTERVAL  rn DAY) as new_date
 from (
select a.author_id,
a.answer_date,
a.author_level,
row_number() over(partition by a.author_id order by a.answer_date) rn
 from (
select 
t1.author_id,
t1.answer_date,
t2.author_level
 from answer_tb t1 
inner join author_tb t2
on t1.author_id=t2.author_id
group by t1.author_id,
t1.answer_date,
t2.author_level
order by t1.author_id,t1.answer_date
) a
) b ) c group by c.author_id,c.author_level,c.new_date
having count(*)>=3