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

WITH TABLEA AS (
SELECT author_id,MAX(CA) AS CA
FROM (
SELECT 
author_id,answer_date-RANKD AS DSIN,COUNT(answer_date)   AS CA -- 计算每一个时段的连续签到数
FROM
(
SELECT Distinct
answer_date,author_id,
Dense_Rank() OVER( PARTITION BY author_id ORDER BY answer_date) AS RANKD -- 通过排序确定连续签到数量
FROM
answer_tb) RA1
GROUP BY author_id,DSIN )  AS FUOD
GROUP BY author_id 
)

SELECT author_id,author_level,CA
FROM author_tb
JOIN TABLEA
USING(author_id)
WHERE CA >=3