题解 | 最长连续登录天数

SELECT
    user_id,
    MAX(datee) AS max_consec_days
FROM (
    SELECT 
        user_id,
        MAX(d_rank) - MIN(d_rank)+1 AS datee
    FROM (
            SELECT 
                user_id,
                fdate,
                ROW_NUMBER() OVER(partition by user_id order by fdate) AS d_rank,
                DATE_SUb(fdate,
                interval ROW_NUMBER() OVER(partition by user_id order by fdate) 
                day) AS start_day
            FROM tb_dau
            WHERE fdate BETWEEN '2023-1-1' AND '2023-1-31'
        ) AS t1
    GROUP BY user_id,start_day  
) AS t2
GROUP BY user_id