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


# 对answer_tb按用户分页开窗,以日期排序,计算最大连续回答问题的天数
# 直接在用户内开窗,lead找到下一个记录的,相减,如果是1就是1,不是1就是null,然后计数多少个1就是多少个1+1

# 开窗
WITH temp_0 AS(
    SELECT author_id, answer_date, LEAD(answer_date) OVER(PARTITION BY author_id ORDER BY answer_date) next_date
    FROM answer_tb
    )
# 针对差是1的,那就是连续的,不是1的全部为null
SELECT author_id, author_level,
        SUM(CASE TIMESTAMPDIFF(DAY, answer_date, next_date) WHEN 1 THEN 1 ELSE NULL END)+1 days_cnt 
FROM temp_0
JOIN author_tb USING(author_id)
GROUP BY author_id, author_level
# 过滤掉小于3的,也就过滤了空值
HAVING days_cnt >= 3
ORDER BY author_id