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

select a.author_id,author_level,days_cnt -- 第五步：多表连接，得到结果
from(
    select author_id,
           base_dt,
           count(1) days_cnt -- 第四步：以用户id，基准日期分组，求连续登录天数
    from ( 
        select 
                author_id,
                date_sub(answer_date,interval rnk DAY) base_dt -- 第三步：作差，得到基准日期
        from (
            select answer_date,  -- 第二步：窗口函数：以用户id分组，日期排序 rnk
           
                    author_id,
                    row_number()over(partition by author_id order by answer_date) rnk
            from (
                select answer_date, -- 第一步：以日期、用户id去重 
                        author_id
                     
                from answer_tb
                group by answer_date, author_id
                    ) t 
         ) t 
    ) t
    group by author_id,base_dt
    having count(1) >=3 -- 连续登录天数大于3天
      ) a 
inner join author_tb b 
on a.author_id = b.author_id
order by author_id;