SQL查询统计用户连续3天及以上登录的连续天数与起止日期

现有存储用户每日登录状态的数据库表，按自然日记录全量用户当日登录情况，表结构包含3个字段：

• id：用户ID
• logged_in：当日登录状态，取值Y为已登录、N为未登录
• date：记录对应的自然日期

需要编写SQL查询，筛选所有用户连续登录3天及以上的连续登录片段，返回结果需包含：对应片段的用户ID、连续登录总天数、连续登录起始日期、结束日期，需支持同一用户存在多段独立连续登录片段的场景。

样例逻辑说明：
源表样例数据中，用户1022在2022/7/11-7/12登录、7/13未登录、7/14-7/16登录；用户2519在2022/8/20-8/24登录、8/25未登录。
期望输出共2条记录：用户1022连续登录3天，起止日期为2022/7/14至2022/7/16；用户2519连续登录5天，起止日期为2022/8/20至2022/8/24，其余连续登录不足3天的片段无需返回。

连续登录片段识别的核心是分组键构造：

1. 先过滤所有未登录记录，仅保留logged_in = 'Y'的登录日数据
2. 以用户为分区，按登录日期升序为每条记录分配递增行号
3. 用「当前记录日期 - 该行号对应的天数」生成分组键：同一连续登录片段内的所有记录，这个分组键值完全相同；断登后重新计数的登录片段，分组键值会发生跳变，自然区分不同的连续段
4. 按用户ID+分组键聚合，计算每段的登录总天数、起始日期、结束日期
5. 最后过滤出总登录天数≥3的片段即可

以下代码基于MySQL语法编写，其他数据库仅需替换日期计算的相关函数即可复用核心逻辑：

WITH login_with_group AS (
    SELECT
        id,
        date,
        -- 构造连续段分组键
        DATE_SUB(
            date,
            INTERVAL ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) DAY
        ) AS streak_mark
    FROM user_daily_login
    WHERE logged_in = 'Y'
)
SELECT
    id AS user_id,
    COUNT(*) AS continuous_login_days,
    MIN(date) AS streak_start_date,
    MAX(date) AS streak_end_date
FROM login_with_group
GROUP BY id, streak_mark
HAVING COUNT(*) >= 3
ORDER BY id, streak_start_date;

注意：如果使用PostgreSQL，可将DATE_SUB(date, INTERVAL x DAY)替换为date - x * INTERVAL '1 day'；SQL Server可替换为DATEADD(day, -x, date)，窗口函数逻辑无需修改。

内容的提问来源于stack exchange，提问作者n0sound