题解 | #某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级#
某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级
http://www.nowcoder.com/practice/e080f8a685bc4af3b47749ca3310f1fd
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
CVTE公司福利 264人发布
