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

select distinct author_id, author_level, max(days_cnt) as days_cnt
from
(select  c.author_id, d.author_level, count(date2) as days_cnt
from
(select *, date_sub(answer_date, interval num day) as date2
from
(select author_id, answer_date, 
row_number() over (partition by author_id order by answer_date) as num from 
(select author_id, answer_date
from answer_tb
group by author_id,answer_date) a
) b
) c
left join author_tb d on c.author_id = d.author_id
group by c.author_id, date2, author_level
) e
group by author_id, author_level
having days_cnt >= 3
order by author_id