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

with
    t2 as (
        select
            t1.author_id,
            count(answer_date) as days_cnt
        from
            (
              -- 2.使用窗口函数  dense_rank按用户分组，对记录按照日期升序排序 as rk
			  -- 依据：连续回答问题的用户的日期-rk得到的结果是相同的【原理：连续登陆间隔相同】
			  select
                    author_id,
                    answer_date,
                    answer_date - dense_rank() over (
                        partition by
                            author_id
                        order by
                            answer_date
                    ) as group_diff
                from
                    (  -- 1.记录去重，保证每日每个户只有一条记录
                        select distinct
                            answer_date,
                            author_id
                        from
                            answer_tb
                    ) t
            ) t1
	  -- 3. 按照author_id,group_diff分组统计，并筛选出连续回答超过3次的用户
        group by
            t1.author_id,
            t1.group_diff
        having
            count(answer_date) >= 3
    )
	-- 4.最后关联author_tb得到用户的等级
select
    t2.author_id,
    a.author_level,
    days_cnt
from
    t2
    left join author_tb a on t2.author_id = a.author_id