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

with b as(select author_id, answer_date, row_number() over(partition by author_id ORDER BY answer_date) rnb
FROM (select distinct author_id, answer_date
from answer_tb) a)#去重排序

select c.author_id, d.author_level, count(c.dt2) days_cnt FROM(SELECT *, date_sub(b.answer_date, interval b.rnb day) dt2 FROM b) c 
#时间与序号做差，差一样的则是连续的，一样的数字有x个，则连续天数为x天，所以计数
left join author_tb d on c.author_id=d.author_id
GROUP BY c.author_id, d.author_level
having count(c.dt2)>=3
order by author_id

去重，排序，做差，计数