首先我们要了解如何去判断连续天数,如果是连续的日期,那么日期-日期对应的行数,所求出的日期是一样的值,基于这个可以找出
连续3天回答问题的用户,
select answer_date,author_id, dense_rank()over(partition by author_id order by answer_date) as cnt from answer_tb group by answer_date,author_id然后将其与问答创作者信息表author_tb连接即可,完整代码如下
select t2.author_id,author_level,t2.day_cnt from (select author_id,count(*) day_cnt from (select answer_date,author_id, dense_rank()over(partition by author_id order by answer_date) as cnt from answer_tb group by answer_date,author_id ) t1 group by author_id,date_sub(answer_date,interval cnt day) having count(*)>=3 ) t2 join author_tb on t2.author_id=author_tb.author_id order by t2.author_id;