题解 | #某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级#
某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级
http://www.nowcoder.com/practice/e080f8a685bc4af3b47749ca3310f1fd
1）建立子查询：先关联author_tb表，获取用户的author_level字段，并对用户每天的回答进行去重排序。
SELECT DISTINCT author_id,answer_date,author_level,DENSE_RANK()OVER(PARTITION BY author_id ORDER BY answer_date)rk FROM answer_tb JOIN author_tb USING(author_id)
SELECT author_id,author_level,COUNT(1) days_cnt FROM (SELECT DISTINCT author_id,answer_date,author_level,DENSE_RANK()OVER(PARTITION BY author_id ORDER BY answer_date)rk FROM answer_tb JOIN author_tb USING(author_id) )t1 GROUP BY author_id,author_level,DATE_SUB(answer_date,INTERVAL rk DAY) HAVING COUNT(1)>=3 ORDER BY author_id;
SQL解题集 文章被收录于专栏
这是牛客SQL相关的解题集
格力公司福利 356人发布
