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

-- 统计最大连续回答问题的天数大于等于3天的用户及其等级（若有多条符合条件的数据，按author_id升序排序）

-- 1 利用窗口函数，将日期减去row_number，将连续日期置于同一日期，便于后续分组(结果作为t1)
# select
#     answer_date,
#     author_id,
#     answer_date-row_number() over (partition by author_id order by answer_date) as tmp_date
# from answer_tb
# group by author_id, answer_date

-- 2 group by, having分组并筛选连续日期>=3的用户(结果作为t2)
# select
#     author_id,
#     count(*) days_cnt
# from t1 
# group by tmp_date, author_id
# having count(*)>=3

-- 3 join合并，得出level
# select
#     t2.author_id,
#     author_level,
#     days_cnt
# from t2 left join author_tb at on t2.author_id=at.author_id

-- 汇总
# select
#     t2.author_id,
#     author_level,
#     days_cnt
# from (
#     select
#     author_id,
#     count(*) days_cnt
# from (
#     select
#         answer_date,
#         author_id,
#         answer_date-row_number() over (partition by author_id order by answer_date) as tmp_date
#     from answer_tb
#     group by author_id, answer_date
# )t1 
# group by tmp_date, author_id
# having count(*)>=3
# )t2 left join author_tb at on t2.author_id=at.author_id

-- 优化：由于author_level对于每个用户是固定且唯一的，故group by时加上author_level可在第二步直接提取author_level（前面忘了加order by，此处加上）
select
    t1.author_id,
    author_level,
    count(*) days_cnt
from (
    select
        answer_date,
        author_id,
        answer_date-row_number() over (partition by author_id order by answer_date) as tmp_date
    from answer_tb
    group by author_id, answer_date
)t1 left join author_tb at on t1.author_id=at.author_id
group by tmp_date, author_id, author_level
having count(*)>=3
order by t1.author_id