题解 | #某乎问答最大连续回答问题天数大于等于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;
全部评论

相关推荐

点赞 评论 收藏
分享
05-25 10:45
西华大学 Java
Frank_zhang:没实习一个项目肯定不够,可以再做一个轮子,技术栈再补一个mq,微服务,整体再换个简历模板,暑期尽量再找一个日常实习
点赞 评论 收藏
分享
评论
点赞
1
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务