题解 | #某乎最大连续回答问题天数大于等于3天的用户信息#

# 连续回答问题的天数大于等于3天的用户
# 取最大
SELECT d.author_id, e.author_level, d.days_cnt
FROM(
    SELECT c.author_id, MAX(c.continue_day) AS days_cnt
    FROM(
        SELECT *,
            ROW_NUMBER() OVER (PARTITION BY b.author_id ORDER BY b.answer_date) AS continue_day
        FROM(
            SELECT *, date_sub(a.answer_date,INTERVAL day_rk DAY) as datediff
            FROM(
                SELECT author_id, answer_date,
                    ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY answer_date) AS day_rk
                FROM answer_tb 
                GROUP BY author_id, answer_date
                ) AS a
            ) AS b
        ) AS c
    GROUP BY c.author_id
    HAVING MAX(c.continue_day) >=3
    ) AS d
LEFT JOIN author_tb AS e ON d.author_id=e.author_id
;