流失与回归查询
识别已经离开的用户，以及间隔一段时间后回归的用户。
流失与回归查询 是 CoddyKit 上的免费 Coding Interview Prep 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Coding Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Coding Interview Prep 课程共包含 4 节课。
留存的另一面
如果留存衡量的是谁留下来，流失衡量的就是谁离开，而回流衡量谁重新回来。面试官经常将这些指标与留存放在一起考查，因为它们能揭示您是否能够推理活动的缺失，而这比统计活动的存在更困难。
反复出现的关键点是：您无法筛选不存在的行。流失查询的核心，是找出用户最后一次活动与现在之间（或与下一次活动之间）的间隔。
精确定义流失
没有时间窗口，“流失”就没有意义。一个常见定义是：如果用户过去 30 天内没有任何活动，就认为该用户已经流失。30 天的不活跃阈值是您必须明确确定的业务选择。
对于订阅产品，流失也可能意味着订阅被取消或过期，是状态变化而不是活动间隔。在编写 SQL 之前，请先明确适用的是哪种模型。
每位用户的最后一次活动
基于活动间隔的流失分析，其基础是每位用户的最近一次事件。按用户分组，并对事件日期取 MAX。
将这个单一值与今天进行比较，就能知道用户已经沉默了多久。后续所有逻辑，都是围绕这个最后出现日期进行比较。
SELECT
user_id,
MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;流失用户查询
如果用户最后一次活动发生在 30 天以前，就认为该用户已经流失。将 last_active 与 CURRENT_DATE - 30 进行比较。最近一次事件早于该截止时间的用户，都已经停止活动。
请注意，相关处理发生在聚合之后：先将数据缩减为每位用户一行，再检查间隔。按日期筛选原始事件只能告诉您谁在某个窗口内不活跃，而不能告诉您谁整体上已经流失。
WITH last_seen AS (
SELECT user_id, MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id
)
SELECT user_id, last_active
FROM last_seen
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';计算流失率
流失率等于流失用户数除以相关基数，通常是该周期开始时处于活跃状态的用户数。使用条件聚合，在一次处理中同时统计流失用户数和总用户数，然后使用 100.0 和 NULLIF 谨慎地进行除法。
在面试中请明确分母：以全部历史用户为基数的流失率，与以此前活跃用户为基数的流失率，是不同的指标。
WITH last_seen AS (
SELECT user_id, MAX(event_at::date) AS last_active
FROM events GROUP BY user_id
)
SELECT
COUNT(*) FILTER (
WHERE last_active < CURRENT_DATE - INTERVAL '30 days'
) AS churned,
COUNT(*) AS total_users,
ROUND(100.0 * COUNT(*) FILTER (
WHERE last_active < CURRENT_DATE - INTERVAL '30 days')
/ NULLIF(COUNT(*), 0), 1) AS churn_pct
FROM last_seen;使用集合逻辑计算环比流失
还可以从另一个角度思考：哪些用户上个月活跃，但本月不活跃？这是一个集合差集问题。先构建上月活跃用户集合和本月活跃用户集合，然后找出属于前一个集合但不属于后一个集合的成员。
您可以使用 EXCEPT、LEFT JOIN / IS NULL 反连接，或者 NOT EXISTS 来表达。反连接最具可移植性，也是面试官最常希望看到的写法。
WITH last_month AS (
SELECT DISTINCT user_id FROM events
WHERE event_at >= DATE '2024-04-01' AND event_at < DATE '2024-05-01'
),
this_month AS (
SELECT DISTINCT user_id FROM events
WHERE event_at >= DATE '2024-05-01' AND event_at < DATE '2024-06-01'
)
SELECT user_id FROM last_month
EXCEPT
SELECT user_id FROM this_month;反连接形式
与本周期流失查询相同，只是改用反连接：将本月活跃用户 LEFT JOIN 到上月活跃用户上，然后保留匹配结果为 NULL 的行。这些用户上月存在、但本月缺席，正是流失用户。
NOT EXISTS 同样是很好的答案，而且能安全处理 NULL 值。需要指出，如果内部集合可能包含 NULL，NOT IN 就有风险，这是一个经典陷阱。
SELECT lm.user_id
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id
WHERE tm.user_id IS NULL;定义回流
回流（也称重新激活）指曾经流失、后来又重新活跃的用户。其典型特征是时间线中出现间隔：先处于活跃状态，随后沉默的时间超过流失阈值，之后又重新活跃。
因此，本月回流的用户是指当前处于活跃状态、上个周期不活跃，但在更早的某个周期有过活动的用户。这是流失的镜像情况。
使用 LAG 检测间隔
查找回流的优雅方法是使用LAG 窗口函数：对于每个用户的每个活动周期，查看其上一个活跃周期。如果两者之间的间隔超过阈值，那么当前周期就是一次重新激活。
LAG 无需自连接，读起来也很清晰。按用户分区，按活跃周期使用 ORDER BY，然后将每个周期与其前一个周期进行比较。
WITH monthly AS (
SELECT DISTINCT user_id,
DATE_TRUNC('month', event_at) AS active_month
FROM events
),
gaps AS (
SELECT user_id, active_month,
LAG(active_month) OVER (
PARTITION BY user_id ORDER BY active_month
) AS prev_month
FROM monthly
)
SELECT user_id, active_month AS resurrected_month
FROM gaps
WHERE prev_month IS NOT NULL
AND active_month > prev_month + INTERVAL '1 month';新用户、回流用户与留存用户
完整的活动分类查询会将本周期的每个活跃用户标记为以下类型之一：新用户（之前没有活动）、留存用户（上个周期也活跃）或回流用户（之前有活动，但中间存在间隔）。LAG 得到的 prev_month 会决定这三种分类。
prev_month IS NULL→ 新用户prev_month = active_month - 1→ 留存用户- 否则（存在间隔）→ 回流用户
给出这样的完整细分，就是一个有力且完整的答案。
SELECT user_id, active_month,
CASE
WHEN prev_month IS NULL THEN 'new'
WHEN active_month = prev_month + INTERVAL '1 month' THEN 'retained'
ELSE 'resurrected'
END AS user_state
FROM gaps;NOT IN 与 NULL 陷阱
最后还有一个地雷。如果您将流失用户写成 WHERE user_id NOT IN (SELECT user_id FROM this_month)，而该子查询哪怕只返回一个 NULL，整个结果也会变为空，因为 NOT IN 针对 NULL 求值时会得到 UNKNOWN。
请优先使用 NOT EXISTS，或使用 LEFT JOIN / IS NULL 反连接；它们都能正确处理 NULL。在留存分析面试中，主动指出这一差异，是体现资深程度的可靠信号。
-- safe anti-join instead of NOT IN
SELECT lm.user_id
FROM last_month lm
WHERE NOT EXISTS (
SELECT 1 FROM this_month tm
WHERE tm.user_id = lm.user_id
);快速检查
您想找出上个月活跃、但本月不活跃的用户。一位同事写了 WHERE user_id NOT IN (SELECT user_id FROM this_month)，结果返回零行，尽管显然有一些用户已经流失。最安全的修复方式是什么？
回顾：流失与回流
流失与回流的要点：
- 使用不活跃阈值定义流失（例如连续 30 天没有活动），或者根据订阅状态变化定义——请明确采用哪一种。
- 计算每位用户的MAX（最近一次活动），然后与
CURRENT_DATE - threshold比较。 - 按周期比较的流失本质上是集合差集：使用 EXCEPT、NOT EXISTS，或 LEFT JOIN / IS NULL 反连接。
- 回流表现为时间线中的间隔；使用
LAG将用户分类为新用户、留存用户或回流用户。 - 可能存在 NULL 时避免使用
NOT IN——它会在不提示的情况下使结果为空。
常见问题解答
「流失与回归查询」课时是免费的吗？
是的 — 「流失与回归查询」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Coding Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 Coding Interview Prep 课程共包含 4 节课。
「流失与回归查询」这节课中我会学到什么？
识别已经离开的用户，以及间隔一段时间后回归的用户。 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 Coding Interview Prep 需要有经验吗？
无需任何先前经验。CoddyKit 上的 Coding Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。
「流失与回归查询」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 Coding Interview Prep 课中编写并运行代码吗？
能。每节 Coding Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。