题解 | #某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级#
某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级
http://www.nowcoder.com/practice/e080f8a685bc4af3b47749ca3310f1fd
select a.author_id,
max(author_level),
max(case when 连续标识 = 1 then ranking end) as days_cnt
from
(select author_id,answer_date,
dense_rank() over(partition by author_id order by answer_date) as ranking,
case when datediff(t1.answer_date,(select max(answer_date)
from answer_tb where t1.author_id = answer_tb.author_id and
answer_tb.answer_date < t1.answer_date)) = 1
then 1 else 0 end
as 连续标识
from
answer_tb t1) a
join author_tb on a.author_id = author_tb.author_id
group by a.author_id
having days_cnt >= 3;