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

WITH a AS(
    SELECT 
       author_tb.author_id,
       author_tb.author_level,
       answer_tb.answer_date
    FROM author_tb
    JOIN answer_tb USING(author_id)
),
b AS(
    SELECT
        author_id,
        author_level,
        answer_date,
        DENSE_RANK() OVER(PARTITION BY author_id ORDER BY answer_date) rk
    FROM a
),
c AS(
    SELECT
        author_id,
        author_level,
        answer_date,
        DATE_SUB(answer_date,INTERVAL rk DAY) date_tmp,
        DENSE_RANK() OVER(PARTITION BY author_id,DATE_SUB(answer_date,INTERVAL rk DAY) ORDER BY answer_date) coin_days
    FROM b
)
SELECT
    author_id, author_level, MAX(coin_days) days_cnt
FROM c
GROUP BY author_id,author_level
HAVING MAX(coin_days) >= 3
ORDER BY author_id