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

#思路：①先准备一张底表tb1，两表连接后将去重后的日期与日期排序做差，形成一列用于分组计算的日期dt；
#②基于tb1，用uid和dt分组并计算连续天数，选出3天以上的部分，形成tb2；
#③基于tb2，用uid分组选出连签天数最大值部分即可。

with tb1 as
(select distinct author_id, answer_date, author_level,
date_sub(answer_date, interval (dense_rank()over(partition by author_id order by answer_date)) day) dt
from answer_tb left join author_tb using(author_id))

select author_id, author_level, max(num)
from(
select author_id, author_level, count(dt) num
from tb1
group by author_id, author_level, dt
having num>=3) as tb2
group by author_id, author_level
order by author_id