请写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天的用户，非常适合于分析用户的活跃度和粘性。