题解 | #天数大于等于3天的用户及其对应等级#

WITH t2 as (SELECT author_id,sum(IF(day(answer_date_up)-day(answer_date)=1,1,0))+1 days_cnt from 
(SELECT author_id,answer_date,lead(answer_date,1) over(PARTITION by author_id ORDER BY answer_date) answer_date_up
from answer_tb) t1
GROUP BY author_id)

SELECT t2.author_id,author_level,days_cnt from 
t2 LEFT JOIN author_tb USING(author_id) 
where days_cnt>=3

题目要求是连续3天,和求出最大连续天数,所以第一次写的求最大连续天数就有问题了,下面只能找出连续三天,但是无法求出连续天数

select DISTINCT t1.author_id author_id from 
answer_tb t1,answer_tb t2,answer_tb t3
where t1.author_id = t2.author_id and t2.author_id = t3.author_id
and day(t1.answer_date) = day(t2.answer_date)-1 and day(t2.answer_date) = day(t3.answer_date)-1

所以需要我们使用窗口函数lead() over();lead(字段名，n) over () :取值向后偏移n行

在窗口函数中使用作者id分组,按照日期排序,这样只需要筛选出,窗口字段减去日期字段=1就能判断出是否是连续的日期了,最后只需要将连续的天数个数加起来,判断是否>=3,结果就出来了