题解 | 最长连续登录天数

with
    distinct_login as (
        select
            fdate,
            user_id
        from
            tb_dau
        group by
            fdate,
            user_id
    ),
    rnk_diff as (
        select
            *,
            date_sub(fdate,interval row_number() over (partition by user_id order by fdate) day ) as fix_date
        from
            distinct_login
    ),
    consecutive_login as (
        select
            user_id,
            fix_date,
            count(1) as consecutive_login_days
        from
            rnk_diff
        group by
            user_id,
            fix_date
    )
SELECT
    user_id,
    max(consecutive_login_days) max_consec_days
FROM
    consecutive_login
GROUP BY
    user_id

数据去重（distinct_login 子查询）

tb_dau 表可能存在同一用户在同一天多次登录的情况，而我们只关心用户是否在某一天登录过，不需要重复记录。因此使用 GROUP BY 对 fdate（日期）和 user_id（用户 ID）进行分组，这样就可以去除重复的登录记录，确保每个用户在每一天只有一条记录。

找出连续登录的区间（rnk_diff 子查询）

要判断用户的登录是否连续，我们可以利用一个巧妙的方法。

对于每个用户，按照登录日期进行排序，然后给每个登录日期分配一个行号。

用登录日期减去对应的行号，如果登录是连续的，那么得到的结果（fix_date）是相同的。

假设用户 A 在 2024-01-01、2024-01-02、2024-01-03 登录，对应的行号分别是 1、2、3。2024-01-01 - 1 天 = 2023-12-31，2024-01-02 - 2 天 = 2023-12-31，2024-01-03 - 3 天 = 2023-12-31，这说明这三天是连续登录的。如果用户 A 在 2024-01-05 又登录了，2024-01-05 - 4 天 = 2024-01-01，与前面的 fix_date 不同，说明这是一个新的连续登录区间。

统计每个连续登录区间的天数（consecutive_login 子查询）

在 rnk_diff 子查询中，我们已经找出了每个用户的连续登录区间（通过相同的 fix_date 标识）。现在，我们要统计每个连续登录区间的天数。使用 GROUP BY 对 user_id 和 fix_date 进行分组，然后使用 COUNT(1) 统计每个分组中的记录数，这个记录数就是该连续登录区间的天数。

找出每个用户的最长连续登录天数（最终查询）

在 consecutive_login 子查询中，我们得到了每个用户的所有连续登录区间及其对应的天数。现在，我们要找出每个用户的最长连续登录天数。使用 GROUP BY 对 user_id 进行分组，然后使用 MAX(consecutive_login_days) 找出每个用户分组中的最大连续登录天数。