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

# 3.22 9:28~ 9:44  16min

# 字段：author_id，author_level，days_cnt
# 核心：连续问题的核心是  排序编号 和 日期 之差为定值则实为连续状态

# tb1：标记连续编号
with tb1 as(
    select answer_date,author_id,author_level,
    dense_rank()over(partition by author_id order by answer_date) as rk
    from answer_tb left join author_tb using(author_id)
),

# tb2：编号与日期做差 (需要去重，distinct)
tb2 as(
    select distinct answer_date,author_id,author_level, rk,
    date_sub(answer_date,interval rk day) as label
    from tb1
),

# tb3：计算每个用户的 连续天数
tb3 as(
    select author_id,author_level,count(label) as days_cnt
    from tb2
    group by author_id,author_level
    having count(label) >= 3
    order by author_id
)

#tb4：求取其中最大的连续天数
select author_id,author_level, max(days_cnt) as days_cnt
from tb3
group by author_id,author_level
order by author_id