题解 |最大连续回答问题天数大于等于3天的用户

# 二刷：
# tb1 : 连接完表 、 构造连续编号
with tb1 as(
   select distinct author_id,author_level,answer_date ,
      ## 按照登录进行排序 
   dense_rank()over(partition by author_id,author_level order by answer_date asc) as rk,
      ## 构造日期answer_date和编号rk的差值
   date_sub(answer_date,interval dense_rank()over(partition by author_id,author_level order by answer_date asc) day) as diff
   from answer_tb left join author_tb using(author_id)
   ),

# tb2 : 按照author_id和diff进行分组
tb2 as(
   select author_id,author_level,count(*) as ct 
   from tb1
   group by author_id,author_level,diff
   )
# tb3 : 选取最大登录天数大于等于3的用户
select author_id,author_level,max(ct) as max_ct
from tb2
group by  author_id,author_level
having max_ct >= 3