请写sql满足业务：找到连续登录3天以上的用户

为了找到连续登录超过 3 天的用户，我们可以使用 SQL 窗口函数和递归查询来实现。假设有一个 user_logins 表，包含以下字段：

• user_id（用户ID）
• login_date（登录日期）

假设 login_date 是 DATE 类型，下面是实现该需求的 SQL 查询：

1. 数据库表结构与样本数据

CREATE TABLE user_logins (
    user_id INT,
    login_date DATE
);

INSERT INTO user_logins (user_id, login_date) VALUES
(1, '2023-07-01'), (1, '2023-07-02'), (1, '2023-07-03'),(1, '2023-07-05'),
(2, '2023-07-01'), (2, '2023-07-03'), (2, '2023-07-04'),(2, '2023-07-05');

2. SQL 查询

WITH login_streaks AS (
    SELECT
        user_id,
        login_date,
        login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY AS streak_id
    FROM
        user_logins
),
streak_groups AS (
    SELECT
        user_id,
        COUNT(*) AS streak_length
    FROM
        login_streaks
    GROUP BY
        user_id, streak_id
)
SELECT DISTINCT
    user_id
FROM
    streak_groups
WHERE
    streak_length > 3;

解析：

这段SQL代码通过使用公用表表达式（CTE）计算用户的连续登录天数，并筛选出连续登录天数大于等于3天的用户。我们将逐步解析这段SQL代码的每个部分。

1. WITH子句和CTE

SQL代码使用了两个CTE：login_streaks 和 streak_groups。

CTE 1：login_streaks

WITH login_streaks AS (
    SELECT
        user_id,
        login_date,
        login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY AS streak_id
    FROM
        user_logins
),

目的：计算每个用户的登录日期，并为每个用户生成一个“连续登录标识符”（streak_id）。

关键点：

• ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)：为每个用户按login_date排序的每一行生成一个唯一的行号。
• login_date - INTERVAL ROW_NUMBER() DAY：通过减去行号的天数来生成一个“连续登录标识符”。如果用户在连续的日期登录，减去行号后得到的结果将是相同的。

例如：

• 假设用户在 2024-07-01 和 2024-07-02 登录，那么：
  • 2024-07-01 - INTERVAL 1 DAY = 2024-06-30
  • 2024-07-02 - INTERVAL 2 DAY = 2024-06-30
  • 这两个记录的“连续登录标识符”将是相同的 2024-06-30，表明它们是连续登录的。

CTE 2：streak_groups

streak_groups AS (
    SELECT
        user_id,
        COUNT(*) AS streak_length
    FROM
        login_streaks
    GROUP BY
        user_id, streak_id
)

目的：计算每个用户的每个“连续登录标识符”对应的连续登录天数。

关键点：

• GROUP BY user_id, streak_id：按用户和“连续登录标识符”分组。
• COUNT(*) AS streak_length：计算每个分组的记录数量，即连续登录的天数。

最终查询

SELECT
    user_id
FROM
    streak_groups
WHERE
    streak_length >= 3;

目的：筛选出连续登录天数大于等于3天的用户。

关键点：

• WHERE streak_length >= 3：只选择连续登录天数（streak_length）大于等于3的用户。

完整解析

1. login_streaks CTE：

   • 为每个用户生成一个包含user_id、login_date和streak_id的临时表。
   • streak_id 标识用户的连续登录，具有相同streak_id的记录表示用户的连续登录序列。

2. streak_groups CTE：

   • 计算每个用户的每个streak_id对应的连续登录天数。
   • 结果表包含user_id和streak_length。

3. 最终查询：

   • 从streak_groups中筛选出streak_length大于等于3的用户。

示例数据和结果

假设user_logins表包含以下数据：

user_id	login_date
1	2024-07-01
1	2024-07-02
1	2024-07-03
1	2024-07-05
2	2024-07-01
2	2024-07-03
2	2024-07-04
2	2024-07-05

Step 1: login_streaks CTE结果：

user_id	login_date	streak_id
1	2024-07-01	2024-06-30
1	2024-07-02	2024-06-30
1	2024-07-03	2024-06-30
1	2024-07-05	2024-07-02
2	2024-07-01	2024-06-30
2	2024-07-03	2024-07-01
2	2024-07-04	2024-07-01
2	2024-07-05	2024-07-01

Step 2: streak_groups CTE结果：

user_id	streak_length
1	3
1	1
2	1
2	3

Step 3: 最终查询结果：

user_id
1
2

总结

这段SQL代码通过使用两个CTE，先计算每个用户的连续登录天数，然后筛选出连续登录天数大于等于3天的用户，非常适合于分析用户的活跃度和粘性。