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

SELECT
author_id,
author_level,
MAX(days_cnt) AS days_cnt

FROM(
    SELECT
    author_id,
    author_level,
    DENSE_RANK() OVER(PARTITION BY author_id, DATE_SUB(answer_date, INTERVAL rk DAY) ORDER BY answer_date) AS days_cnt

    FROM(
        SELECT
        author_id,
        author_level,
        answer_date,
        DENSE_RANK() OVER(PARTITION BY author_id ORDER BY answer_date) AS rk

        FROM answer_tb AS an
        LEFT JOIN author_tb AS au
            USING(author_id)) AS t1) AS t2

WHERE days_cnt >= '3'

GROUP BY author_id, author_level

ORDER BY author_id