题解 | #最长连续登录天数#

WITH login_gaps AS (
-- 获取上一行数据日期
    SELECT 
        user_id,
        fdate,
        LAG(fdate) OVER (PARTITION BY user_id ORDER BY fdate) AS previous_fdate
    FROM 
        tb_dau
),
marked_logins AS (
-- 标识不连续的登录。
-- 不连续的登录意味着当前登录日期与前一次登录日期相差超过 1 天。我们可以为这种情况打一个标记。
    SELECT
        user_id,
        fdate,
        CASE 
            WHEN previous_fdate IS NULL OR DATEDIFF(fdate, previous_fdate) > 1 THEN 1
            ELSE 0
        END AS new_group_flag
    FROM 
        login_gaps
),
grouped_logins AS (
    SELECT
        user_id,
        fdate,
        SUM(new_group_flag) OVER (PARTITION BY user_id ORDER BY fdate) AS group_number
    FROM 
        marked_logins
),
Consecutive_Login_Days AS (
SELECT
    user_id,
    MIN(fdate) AS group_start_date,
    MAX(fdate) AS group_end_date,
    COUNT(*) AS login_days
FROM 
    grouped_logins
GROUP BY 
    user_id, group_number
ORDER BY 
    user_id, group_start_date)
SELECT user_id,max(login_days) as max_consec_days FROM Consecutive_Login_Days GROUP BY user_id;


思路步骤如下：
1.先获取上一次登录日期
2、如果上一次登录日期为null 或 当前登录日期-上一次登录日期 不大于 1天，则标记为 1，大于1 则等于计算出的日期，否则为0
3、通过步骤2 得出的标记进行分组
4.统计每个分组的数据，求出最大数据，该数据则为最长登录天数