SQL面试真题--最大连续登录天数

SQL194 某乎问答最大连续回答问题天数大于等于3天的用户及其对应等应的等级
快手一面和民生Fintech笔试都考到了这个，这是逼迫我半年后再次刷起sql啊

with t as (
  select author_id,
  answer_date,
  dense_rank() over (PARTITION BY author_id order by answer_date) ranking
  from answer_tb
),
t1 as (
  select t.author_id,
  t.answer_date,
  DATE_SUB(answer_date,INTERVAL ranking DAY) as dt
  FROM t
),
continue_df as (
  select t1.author_id,
  count(distinct t1.answer_date) days_cnt  -- 可能存在同一天的多次登录，所以要count(distinct answer_date)
  from t1
  GROUP BY t1.author_id,t1.dt
  having days_cnt>=3
)

select 
  a.author_id,b.author_level,a.max_continue_days 
FROM (
  select author_id,max(days_cnt) as max_continue_days
  from continue_df
  group by author_id
)a
LEFT JOIN author_tb b
on a.author_id=b.author_id
order by a.author_id
;