题解 | 最大连续时间问题

select
    total_a.author_id,
    b.author_level,
    total_a.days_cnt
from
(select
    author_id,
    count(*) as days_cnt
from
(select
    author_id,
    flag
from
(select
    answer_date,
    author_id,
    answer_date - rn as flag
from
(select
    answer_date,author_id,issue_id,
    dense_rank() over(partition by author_id order by answer_date) as rn
from
    answer_tb) rn_data) flag_data
group by
    answer_date,
    author_id) a
group by
    author_id,
    flag
having 
    count(*) >= 3) total_a
left join
    author_tb b
using(author_id)

本质上就是不停的嵌套，关键点在于以下几个：

1. 构造表A，有author_id和days_cnt；
2. 联合现有的表B，提取author_level；
3. 计算days_cnt，有一个核心的算法，就是dense_rank下，flag = date-dense_rank的值，如果连续那么会是同一个值，这种情况下直接计算每个author_id下，各个flag出现的次数，这个group by就可以；
4. 为了防止一日多答，因此需要group by answer_date和author_id，做一个去重处理；