题解 | #天数大于等于3天的用户及其对应等级#
某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级
https://www.nowcoder.com/practice/e080f8a685bc4af3b47749ca3310f1fd
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,结果就出来了
投递蚂蚁集团等公司10个岗位
查看20道真题和解析