题解 | #最大连续回答问题天数大于等于3天

典型的连续问题

1、row_number() over(partition by author_id order by answer_date asc) as "rk"

2、如果3天内连续回答问题，那么这三天的 date_add(answer_date, interval - rk day) as ”辅助列“ 必定是相同的

3、然后

count(distinct answer_date) as "days_cnt" #这个就是连续回答的天数

group by author_id ,辅助列

with a as (
        SELECT 
        author_id,
        count(distinct answer_date) as days_cnt
        from (
                select 
                author_id,
                answer_date,
                #row_number() over(partition by author_id order by answer_date asc) as "rk",
                date_add(answer_date, interval -(dense_rank() over(partition by author_id order by answer_date asc)) day) as "辅助列"
                from answer_tb
        ) as t
        group by author_id,辅助列
        having days_cnt>=3
)

SELECT 
df1.author_id,
df2.author_level,
df1.days_cnt
from a as df1
left join author_tb as df2
on df1.author_id = df2.author_id
order by df1.author_id asc