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

题目有个要求是最大连续回答问题天数大于等于3天，那按题意应该取一次max，留max_dyas_cnt作为days_cnt。

事实上其实只选出days_cnt>=3也没关系，当然是属于最大连续回答问题天数大于等于3的用户，但是如果同一用户有两个这种连续记录那么就会重复（本题没有出现这种情况）,那么days_cnt选取就出现矛盾，所以理解为max_days_cnt作为days_cnt以及选出该用户即可。

select author_id,author_level,max_days_cnt as days_cnt
from (
		select author_id,max(days_cnt) as max_days_cnt
		from (
					select author_id,
								 count(dt2) as days_cnt
					from (
								select author_id,
											 answer_date as dt1,
											 date_sub(answer_date,interval ranking day) as dt2
								from (
									select distinct author_id,
												 answer_date,
												 dense_rank() over(partition by author_id order by answer_date asc) as ranking
									from answer_tb
										 ) as t
							 ) as t1
					group by author_id,dt2
					) as t2
		group by author_id
		having max_days_cnt>=3
) as a
left join author_tb using(author_id)
order by author_id