题解 | #最大连续答题天数大于等于3天的用户及对应等级#
某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级
https://www.nowcoder.com/practice/e080f8a685bc4af3b47749ca3310f1fd
-- 方法一:刚开始的做法,用排序后分组
# with t1 as
# ( -- 先找出需要的字段并去重取出来
# select distinct
# a.author_id,
# answer_date,
# a.author_level
# # row_number() over (partition by author_id) rn
# from answer_tb join author_tb a on answer_tb.author_id = a.author_id
# order by author_id,answer_date
# ),t2 as
# ( -- 排序日期求差值
# select
# t1.*,
# row_number() over (partition by author_id) rn,
# date_add(answer_date,Interval - row_number() over (partition by author_id) day ) s_dt
# from t1)
# select -- 按要求找出答案
# author_id,
# author_level,
# count(distinct answer_date) days_cnt
# from t2
# group by author_id, author_level,s_dt
# having days_cnt>=3
# ;
-- 方法二:后来发现直接按level分组就好了
with t1 as
( -- 先找出需要的字段并去重取出来
select distinct
a.author_id,
answer_date,
a.author_level
# row_number() over (partition by author_id) rn
from answer_tb join author_tb a on answer_tb.author_id = a.author_id
order by author_id,answer_date
),t2 as
( -- 这边可以优化一下,懒得做了,留给有缘人\(^o^)/~
select
t1.*
# row_number() over (partition by author_id) rn,
# date_add(answer_date,Interval - row_number() over (partition by author_id) day ) s_dt
from t1)
select -- -- 按要求找出答案
author_id,
author_level,
count(distinct answer_date) days_cnt
from t2
group by author_id, author_level
having days_cnt>=3
;