题解 | 最长连续登录天数

SELECT
    user_id,
    MAX(con_date) max_consec_days
FROM
    (SELECT
        user_id,
        COUNT(ori_date) con_date
    FROM
        (SELECT
            user_id,
            fdate,
            DATE_SUB(fdate,INTERVAL dn-1 DAY) ori_date
        FROM
            (SELECT 
                user_id,
                fdate,
                DENSE_RANK() OVER(PARTITION BY user_id  ORDER BY fdate) dn
            FROM tb_dau
            WHERE fdate BETWEEN '2023-01-01' AND '2023-01-31'
            ) t_1
        )t_2
        GROUP BY user_id,ori_date
    )t_3
GROUP BY user_id