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

1、首先将两个表内连接，连续问题一半会先创建dense_rank()字段，再和日期相减，结果相同则连续，相同个数即为连续的天数 with a as (select distinct x.author_id,answer_date,author_level,dense_rank() over (partition by x.author_id order by answer_date) as rn from answer_tb x join author_tb y using(author_id)) 2、在1的基础上，根据作者Id以及减去的结果分组求个数，对个数进行限制，排序后可以得到结果 select a.author_id,a.author_level,count(*) as days_cnt from a
group by a.author_id,date_add(a.answer_date,interval -a.rn day),a.author_level having days_cnt>=3 order by 1