题解 | #最大连续答题天数大于等于3天的用户及对应等级#

-- 方法一:刚开始的做法,用排序后分组
# 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
;