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

WITH t1 AS (
    SELECT answer_date,
           author_id
    FROM answer_tb
    GROUP BY answer_date, author_id
),
t2 AS (
    SELECT
        answer_date,
        author_id,
        row_number()
            over (PARTITION BY author_id
                ORDER BY answer_date ASC) AS rank_num
    FROM t1
),
t3 AS (
    SELECT
        DATE_ADD(answer_date, INTERVAL -rank_num DAY ) AS after_date,
        author_id
    FROM t2
),
t4 AS (
    SELECT author_id,
           after_date,
           count(author_id) AS days_cnt
    FROM t3
    GROUP BY author_id, after_date
),
t5 AS (
    SELECT author_id,
           days_cnt
    FROM t4
    WHERE days_cnt >= 3
)
SELECT t5.author_id,
       a.author_level,
       days_cnt
       FROM t5
LEFT JOIN author_tb a
ON t5.author_id = a.author_id
ORDER BY t5.author_id ASC
;

# 连续登录问题，不需要去想间隔问题，按照排序套路即可！