🎯 SQL 热门面试题：连续天数 & 留存率分析

本文整理了 3 道高频 SQL 面试题，聚焦「连续天数统计」「新用户留存率计算」两大核心场景，详细拆解解题思路与代码逻辑，助力掌握数据分析类 SQL 的核心技巧。

📝 面试题 1：统计最大连续回答问题天数≥3 天的创作者

题目背景

某乎平台需分析创作者创作活跃度，筛选出最大连续回答问题天数≥3 天的创作者，展示其编号和等级。

数据表结构

1. 创作者信息表 author_tb

2. 创作者回答情况表 answer_tb

问题要求

• 统计最大连续回答问题天数≥3 天的用户及其等级；

• 结果按author_id升序排序。

示例输入输出

示例输入（简化）

• author_tb：101 号创作者等级为 6；

• answer_tb：101 号创作者在 2021-11-01/02/03 连续 3 天回答问题。

示例输出

解题思路 🧠

核心是识别连续日期段，采用「日期偏移法」（连续天数问题通用解法）：

1. 对每个创作者的回答日期分组排序，生成递增排名；

2. 用「回答日期 - 排名天数」生成 “基准日期”，连续日期会得到相同基准日期；

3. 按「创作者 + 基准日期」分组，统计每组天数（即连续天数）；

4. 筛选连续天数≥3 的记录，关联等级表后排序输出。

SQL 代码详解

sql

SELECT
  a.author_id,
  b.author_level,
  a.days_cnt
FROM
  (
    -- 步骤3：按创作者+基准日期分组，统计连续天数并筛选≥3的记录
    SELECT
      t1.author_id,
      COUNT(DISTINCT t1.answer_date) days_cnt
    FROM
      (
        -- 步骤2：计算基准日期（兼容MySQL语法的日期偏移）
        SELECT
          t.author_id,
          t.answer_date,
          DATE_SUB(t.answer_date, INTERVAL t.ranking DAY) dt
        FROM
          (
            -- 步骤1：按创作者分组，对回答日期排序
            SELECT
              author_id,
              answer_date,
              DENSE_RANK() OVER (
                PARTITION BY author_id
                ORDER BY answer_date
              ) ranking
            FROM
              answer_tb
          ) t
      ) t1
    GROUP BY
      t1.author_id,
      t1.dt
    HAVING
      COUNT(DISTINCT t1.answer_date) >= 3
  ) a
  -- 关联创作者等级表
  LEFT JOIN author_tb b ON a.author_id = b.author_id
-- 按创作者编号升序排序
ORDER BY
  a.author_id;

代码分步解释

1. 内层子查询 t：

   按author_id分组，用DENSE_RANK()对回答日期升序排名（ranking）。例如 101 的 2021-11-01 排名 1、2021-11-02 排名 2、2021-11-03 排名 3。

2. 子查询 t1：

   用DATE_SUB(answer_date, INTERVAL ranking DAY)计算基准日期dt，连续日期的dt相同：

   • 101 的 2021-11-01 - 1 天 = 2021-10-31；

   • 101 的 2021-11-02 - 2 天 = 2021-10-31；

   • 101 的 2021-11-03 - 3 天 = 2021-10-31。

3. 子查询 a：

   按author_id + dt分组，COUNT(DISTINCT answer_date)统计连续天数（去重是因为同一用户单日可能回答多题）；HAVING筛选连续天数≥3 的记录。

4. 外层关联：

   左关联author_tb获取等级，按author_id升序输出。

补充说明 💡

• DENSE_RANK() vs ROW_NUMBER()：日期无重复时效果一致；若有重复日期（如单日多回答），DENSE_RANK()给相同日期相同排名，更贴合 “按天排序” 需求；

• 不同 SQL 方言适配：原代码answer_date - ranking是简化写法，Oracle 需用answer_date - INTERVAL '1' DAY * ranking。

📝 面试题 2：统计 2021 年 11 月每天新用户的次日留存率

题目背景

产品运营需分析新用户留存，计算 2021 年 11 月每天新用户的次日留存率（保留 2 位小数）。

数据表结构

用户行为日志表 tb_user_log

问题要求

• 次日留存率 = 当天新增用户中次日仍活跃的用户数 / 当天新增用户总数；

• 若in_time/out_time跨天，两天均记为用户活跃；

• 仅输出 2021 年 11 月有新用户的日期，按日期升序排列。

示例输入输出

示例输入

示例输出

解题思路 🧠

核心步骤：

1. 确定每个用户的首次活跃日期（新用户日期）；

2. 构建用户全量活跃日期表（含跨天场景）；

3. 关联新用户表和活跃表，统计次日活跃的新用户数；

4. 计算留存率并格式化输出。

SQL 代码详解

sql

SELECT
  t1.dt,
  ROUND(COUNT(DISTINCT t2.uid) / COUNT(DISTINCT t1.uid), 2) AS uv_left_rate
FROM
  (
    -- 子查询1：获取每个用户的首次活跃日期（新用户日期）
    SELECT
      uid,
      MIN(DATE(in_time)) AS dt
    FROM tb_user_log
    GROUP BY uid
  ) t1
LEFT JOIN
  (
    -- 子查询2：构建全量活跃日期表（含in/out时间，去重）
    SELECT uid, DATE(in_time) AS active_dt FROM tb_user_log
    UNION  -- UNION自动去重，避免单日重复统计
    SELECT uid, DATE(out_time) AS active_dt FROM tb_user_log
  ) t2
ON t1.uid = t2.uid 
AND t1.dt = DATE_SUB(t2.active_dt, INTERVAL 1 DAY)  -- 匹配次日活跃
WHERE
  DATE_FORMAT(t1.dt, '%Y-%m') = '2021-11'  -- 限定2021年11月
GROUP BY
  t1.dt
ORDER BY
  t1.dt;

代码分步解释

1. 子查询 t1（新用户表）：

   按uid分组，MIN(DATE(in_time))为用户首次活跃日期（新用户日期）。例如 101 的新用户日期是 2021-11-01，104 是 2021-11-02。

2. 子查询 t2（全量活跃日期表）：

   • 分别提取in_time和out_time的日期，覆盖跨天场景；

   • UNION去重，避免同一用户单日因多条日志被重复统计。

3. 关联条件：

   • t1.uid = t2.uid：匹配同一用户；

   • t1.dt = DATE_SUB(t2.active_dt, 1 DAY)：筛选 “新用户日期的次日活跃” 的用户。

4. 留存率计算：

   • COUNT(DISTINCT t2.uid)：次日活跃的新用户数；

   • COUNT(DISTINCT t1.uid)：当天新增用户总数；

   • ROUND(..., 2)：保留 2 位小数。

补充说明 💡

• UNION vs UNION ALL：必须用UNION，否则同一用户单日会被多次统计，导致留存率计算错误；

• 跨天场景示例：若用户in_time=2021-11-01 23:59、out_time=2021-11-02 00:01，则 11-01 和 11-02 均记为活跃；

• 无新用户的日期（如 11-04）：t1中无该日期，自然不输出。

📝 面试题 3：计算 2023 年 1 月用户最长连续登录天数

题目背景

用户活跃度画像的核心特征是 “最长连续登录天数”，需计算 2023 年 1 月 1 日 - 31 日期间每个用户的该指标。

数据表结构

用户登录表 tb_dau（日活表）

问题要求

统计 2023 年 1 月期间每个用户的最长连续登录天数，输出用户 ID 和对应天数。

示例输入输出

示例输入

示例输出

解题思路 🧠

复用「日期偏移法」识别连续登录段：

1. 对每个用户的登录日期排序，生成行号；

2. 登录日期 - 行号天数 = 基准日期（连续日期基准相同）；

3. 按用户 + 基准日期分组，计算每组连续天数；

4. 按用户分组，取最大连续天数。

SQL 代码详解

sql

SELECT
  user_id,
  MAX(consec_days) AS max_consec_days
FROM
  (
    -- 步骤3：按用户+基准日期分组，计算连续天数
    SELECT
      user_id,
      base_date,
      MAX(rn) - MIN(rn) + 1 AS consec_days
    FROM
      (
        -- 步骤1+2：排序+计算基准日期
        SELECT
          user_id,
          fdate,
          ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) AS rn,
          DATE_SUB(fdate, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) DAY) AS base_date
        FROM tb_dau
        WHERE fdate BETWEEN '2023-01-01' AND '2023-01-31'
      ) t1
    GROUP BY user_id, base_date
  ) t2
GROUP BY user_id
ORDER BY user_id;

代码分步解释（以 10000 为例）

1. 子查询 t1：

   筛选 2023 年 1 月记录，按user_id分组排序生成行号rn，计算基准日期：

   user_id	fdate	rn	base_date
   10000	2023-01-01	1	2022-12-31
   10000	2023-01-02	2	2022-12-31
   10000	2023-01-04	3	2023-01-01

2. 子查询 t2：

   按user_id + base_date分组，计算连续天数：

   • 分组 (10000, 2022-12-31)：2-1+1=2 天；

   • 分组 (10000, 2023-01-01)：3-3+1=1 天。

3. 外层查询：

   取MAX(consec_days)，即 10000 的最长连续登录天数为 2。

补充说明 💡

• 去重处理：若tb_dau存在用户单日多条登录记录，需先执行SELECT DISTINCT user_id, fdate FROM tb_dau；

• 边界场景：

  • 无登录记录的用户：需左关联用户表，默认max_consec_days=0；

  • 整月连续登录：基准日期固定，连续天数 = 31；

  • 仅单日登录：连续天数 = 1。

📚 核心知识点总结

✅ 连续天数问题的核心逻辑：让连续日期生成相同的 “基准日期”，通过分组统计基准日期的天数；

✅ 留存率问题的核心：明确 “新用户日期” 和 “活跃日期” 的关联关系，覆盖跨天等特殊场景。