某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级
某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级
https://www.nowcoder.com/practice/e080f8a685bc4af3b47749ca3310f1fd
with t1 as ( select answer_date, author_id, row_number() over (partition by author_id order by answer_date) as ranks from answer_tb) select t3.author_id, author_level, log_cnt from( select author_id, gap, count(answer_date) as log_cnt from ( select answer_date, author_id, date_sub(answer_date, interval ranks day) as gap from t1 )t2 group by author_id, gap )t3 left join author_tb at on t3.author_id=at.author_id where log_cnt>=3