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

select a.author_id,
       max(author_level),
       max(case when 连续标识 = 1 then ranking end) as days_cnt
       from
(select author_id,answer_date,
       dense_rank() over(partition by author_id order by answer_date) as ranking,
       case when datediff(t1.answer_date,(select max(answer_date) 
                                       from answer_tb where t1.author_id = answer_tb.author_id and
                                       answer_tb.answer_date < t1.answer_date)) = 1
                                       then 1 else 0 end
                                       as 连续标识
       from
       answer_tb t1) a
       join author_tb on a.author_id = author_tb.author_id
       group by a.author_id
       having days_cnt >= 3;