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

with t1 as (
    select distinct answer_date,author_id from answer_tb
),
t2 as (
    select answer_date,
    author_id,
    answer_date-row_number()over(partition by author_id order by answer_date)date1
    from t1
)
select author_id,author_level,count(date1)days_cnt
from author_tb
join t2 using(author_id)
group by author_id,author_level
having days_cnt >= 3
order by author_id asc

#最大连续登录天数

1.对数据进行去重，同一天内可能同一用户会多次登录，用with t1 as ( )得到去重后的数据表即为t1

2.重点:对t1中已经处理过的数据进行进一步处理，解决连续登录问题，用answer_date-row_number()over(partition by author_id order by answer_date)date1，此处的date1即用为解决连续登录问题的关键，若count(date1)>=3则满足要求。

3.注意过滤条件为count(date1)>=3，是聚合函数，不能用where，要用having

要点:使用with ... as ( )语法得到去重后的数据表以及对日期处理后的数据，之后直接从t1,t1表中取出所需数据即可。