大厂数据分析师/数据研发面试题：沉默用户激活与首日复购行为分析

1. 题目定义

题目背景：在电商和用户运营场景中，流失/沉默用户的重新激活（唤醒）并产生转化，是评估运营活动效果的重要指标。
题目要求：编写 SQL 给出满足以下所有条件的用户 ID（user_id）列表：

1. 注册时间超过 30 天。
2. 在过去 30 天内（不含今天）没有过任何登录行为。
3. 今天突然登录，并且在今天产生了购买行为。

2. 模拟数据集

为了便于验证和理解，我们假设今天（计算当天）的日期为 2023-11-01。

表 1：用户注册表 users

记录用户的基本信息及注册时间。

表 2：用户登录日志表 logins

记录用户的历史登录日期。

表 3：订单交易表 orders

记录用户的购买订单。

注：用户 104 今天虽然登录了，但今天没有购买行为，因此不应该出现在最终结果中。

期望输出结果

3. SQL 解决方案

在实际面试中，通常需要写出动态适应当前日期的 SQL（使用系统当前时间函数，如 CURRENT_DATE）。下面提供两种主流的解题思路：子查询排除法 和 聚合过滤法（更推荐，性能更好）。

方案一：子查询/条件过滤法（直观易懂）

此方案严格按照题目字面逻辑，通过 IN 和 NOT IN（或 EXISTS/NOT EXISTS）进行条件拼接。

1. 动态日期版本（生产环境适用）

SELECT DISTINCT u.user_id
FROM users u
-- 条件 1：注册时间超过 30 天
WHERE u.register_date < DATE_SUB(CURRENT_DATE, 30)
  -- 条件 2：今天有登录行为
  AND u.user_id IN (
      SELECT user_id FROM logins WHERE login_date = CURRENT_DATE
  )
  -- 条件 3：过去 30 天内（不含今天）没有登录过
  AND u.user_id NOT IN (
      SELECT user_id FROM logins 
      WHERE login_date >= DATE_SUB(CURRENT_DATE, 30) AND login_date < CURRENT_DATE
  )
  -- 条件 4：今天有购买行为
  AND u.user_id IN (
      SELECT user_id FROM orders WHERE order_date = CURRENT_DATE
  );

2. 静态日期版本（用于跑通上述模拟数据，设今天为 2023-11-01）

SELECT DISTINCT u.user_id
FROM users u
WHERE u.register_date < DATE_SUB('2023-11-01', 30)
  AND u.user_id IN (
      SELECT user_id FROM logins WHERE login_date = '2023-11-01'
  )
  AND u.user_id NOT IN (
      SELECT user_id FROM logins 
      WHERE login_date >= DATE_SUB('2023-11-01', 30) AND login_date < '2023-11-01'
  )
  AND u.user_id IN (
      SELECT user_id FROM orders WHERE order_date = '2023-11-01'
  );

方案二：聚合过滤法（高效，推荐面试使用）

在大数据场景（如 Hive/Spark）下，多次读取大表（logins）会导致性能极差。我们可以通过 JOIN 之后，利用 GROUP BY + HAVING 配合条件计数来实现一步到位。

SELECT u.user_id
FROM users u
-- 用 JOIN 关联登录和订单表（只保留今天登录和购买过的基础人群，大大缩小计算量）
JOIN logins l ON u.user_id = l.user_id
JOIN orders o ON u.user_id = o.user_id
-- 基础粗筛：今天必须有登录，今天必须有订单，且注册超过30天
WHERE u.register_date < DATE_SUB('2023-11-01', 30)
  AND l.login_date = '2023-11-01'
  AND o.order_date = '2023-11-01'
GROUP BY u.user_id
-- 条件过滤：通过 HAVING 确保该用户在过去 30 天内的登录次数为 0
HAVING SUM(CASE WHEN l.login_date >= DATE_SUB('2023-11-01', 30) AND l.login_date < '2023-11-01' THEN 1 ELSE 0 END) = 0;

4. 核心考察知识点与深度解析

① 边界条件与日期计算（Date Functions）

面试官非常注重候选人对日期边界的把控。

• 注册超过 30 天：应写为 register_date < DATE_SUB(today, 30)。不能包含等于，因为“超过 30 天”代表注册时间至少在 31 天及以前。
• 过去 30 天内（不含今天）：时间闭区间为 [today - 30, today - 1]。用 SQL 表达就是 login_date >= DATE_SUB(today, 30) AND login_date < today。

② 处理“不存在”的逻辑：NOT IN vs NOT EXISTS vs LEFT JOIN

这是 SQL 面试的高频性能考点。

• NOT IN 的陷阱：如果子查询的结果集中含有 NULL 值，整个 NOT IN 子查询会返回 NULL，导致外层查不出任何数据。因此在生产环境中使用 NOT IN 时，必须确保子查询中进行了 WHERE column IS NOT NULL 过滤。
• NOT EXISTS 的优势：比 NOT IN 更安全，且在大部分主流 RDBMS（如 MySQL, PostgreSQL）中能利用索引进行半连接（Anti-Join）优化，性能更好。
• LEFT JOIN ... WHERE right_table.key IS NULL：这是大数据引擎（如 Hive）中常用的反连接写法。

③ 性能优化思路（应答加分项）

如果在面试中被问到：“如果 logins 表每天有数十亿条数据，你的 SQL 怎么优化？”

• 回答要点：
  1. 避免全表扫描：不要直接对全量历史 logins 展开 NOT IN 检索。
  2. 先分区裁剪：对 logins 表先通过 WHERE login_date >= DATE_SUB(today, 30) 限制分区范围，过滤掉 30 天前的无效历史数据。
  3. 聚合规避多次扫描：如方案二所示，将“今天登录过”和“过去 30 天没登录过”放在同一次 GROUP BY 中用 COUNT(CASE WHEN...) 来解决，避免了两次扫描大表。