构建留存矩阵
按用户群组和时间偏移统计活跃用户，形成留存表。
构建留存矩阵 是 CoddyKit 上的免费 Coding Interview Prep 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Coding Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Coding Interview Prep 课程共包含 4 节课。
什么是留存矩阵
定义队列之后，下一步就是著名的留存矩阵：行表示队列，列表示周期偏移量（第 0、1、2 个月等），每个单元格统计该队列在对应偏移周期仍然活跃的用户数。
面试官很喜欢这个问题，因为它要求您结合队列分配、连接回活跃记录、周期差计算和透视操作。这是产品分析中最具代表性的查询。
两个输入
您需要两项数据：每位用户的队列周期（来自上一课），以及每位用户每个活跃周期的记录。活跃数据来自同一张事件表，并被压缩到周期粒度。
因此，请按以下方式规划查询：先建立队列 CTE，再建立一个列出每位用户活跃月份的活跃记录 CTE，最后将两者连接起来。
WITH user_cohort AS (
SELECT user_id,
DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events
GROUP BY user_id
)
SELECT * FROM user_cohort;列出活跃周期
活跃记录 CTE 要回答的问题是：“每位用户在哪些月份活跃过？”请将每个事件截断到月份，并使用 DISTINCT 或 GROUP BY 去重，这样一位用户在三月活跃 40 次也只会产生一行三月记录。
这份按用户和月份整理的列表，就是与队列连接、衡量各个偏移周期留存情况的依据。
WITH activity AS (
SELECT DISTINCT
user_id,
DATE_TRUNC('month', event_at) AS active_month
FROM events
)
SELECT * FROM activity;计算周期偏移量
矩阵的核心是周期编号：某次活跃发生在队列开始后的第几个月？计算方法是用活跃月份减去队列月份。
在 Postgres 中，一种清晰的做法是计算两个日期之间相差的完整月份数。可移植的公式是将年份差乘以 12，再加上月份差；许多数据库引擎也提供了相应的辅助函数。偏移量 0 表示队列自己的起始月份。
-- months between two month-truncated dates (Postgres)
SELECT
(EXTRACT(YEAR FROM active_month) - EXTRACT(YEAR FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
AS period_number;将队列与活动连接
将队列 CTE 与活动 CTE 通过 user_id 连接。每一行输出表示：该用户来自队列 X，并且在偏移量 N 对应的周期处于活跃状态。按（队列、偏移量）统计去重后的用户数，就得到了长格式的矩阵。
由于每位队列成员在自己的起始月份都处于活跃状态，因此偏移量 0 应等于队列规模，这是一个内置的合理性检查。
WITH user_cohort AS (
SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events GROUP BY user_id
),
activity AS (
SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;长格式留存表
添加偏移量计算并进行聚合。现在您得到的是整洁的长格式结果：每个队列的每个偏移量各占一行，并包含留存用户数。许多面试官会直接接受这种结果，因为透视只是展示形式上的变化。
请注意，由于偏移量表达式是计算得出的，而不是存储列，因此它同时出现在 SELECT 和 GROUP BY 中。
WITH user_cohort AS (
SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events GROUP BY user_id
),
activity AS (
SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
FROM events
)
SELECT
c.cohort_month,
(EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
+(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;将数据透视为宽列
要得到经典网格，可以使用条件聚合将偏移量透视为列：针对每个偏移量使用一个 CASE，再对结果求 SUM。这种可移植的模式无需特殊的 PIVOT 语法，适用于所有 SQL 方言。
每个 CASE 会在行的周期编号与该列匹配时输出 1，因此 SUM 会统计该偏移量对应的留存用户数。
SELECT
cohort_month,
COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;从计数到留存率
面试官通常希望看到的是百分比，而不是原始计数。将每个偏移量的留存用户数除以队列规模（偏移量 0 的数量）。请使用 CAST 将其转换为浮点数，或乘以 1.0，以避免整数除法；这是这里最常见的隐蔽错误。
结果是一条留存曲线：第 0 个月为 100%，之后逐渐下降并趋向平台期。这个平台期才是利益相关者真正关心的指标。
SELECT
cohort_month,
period_number,
retained_users,
ROUND(
100.0 * retained_users
/ MAX(retained_users) OVER (PARTITION BY cohort_month),
1
) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;整数除法陷阱
这是面试中几乎必考的陷阱：在大多数引擎中，120 / 500 等于0，而不是 0.24，因为两个操作数都是整数。留存百分比会悄无声息地全部变成 0。
解决方法是让其中一侧变为数值类型：乘以 100.0，使用 CAST 将一个操作数转换为 NUMERIC，或者除以 NULLIF(size, 0)，同时防止空队列导致除零。说明“NULLIF 还能防止除零”会为您赢得加分。
SELECT
retained_users,
cohort_size,
100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;用零填充缺失的偏移量
如果某个队列在偏移量 2 处没有留存用户，JOIN 就不会产生对应的行，从而在矩阵中留下空缺。要显示明确的0，请生成所有（队列、偏移量）组合的完整网格，然后将计数 LEFT JOIN 到其中。
可以将队列与数字/偏移量列表进行 CROSS JOIN 来构建网格，然后将缺失的计数合并为零。面试官会很欣赏您注意到了这个缺口。
WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
c.cohort_month, o.period_number,
COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
ON r.cohort_month = c.cohort_month
AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;三角形结构与新近队列偏差
还有一个值得讨论的要点：矩阵呈三角形。上个月才开始的队列目前不可能有第 3 个月的数值，因此越靠后的偏移量，参与计算的队列就越少。
因此，跨队列比较某一列的平均值会偏向较早的队列。请说明您会如实展示这个三角形，或者只比较所有队列都已经达到的偏移量。具备这种意识，才能将分析师与只会编写查询的人区分开来。
快速检查
您的留存查询将留存用户数除以队列规模，但除了第 0 个月之外，每个百分比都显示为 0。最可能的原因是什么？
回顾：留存矩阵
在面试中构建留存矩阵时：
- 为每位用户分配一个队列周期，然后列出每位用户去重后的活跃周期。
- 将两者连接起来，计算周期偏移量（队列周期与活动周期之间相差的月份数）。
- 使用
COUNT(DISTINCT user_id)聚合为长格式；如果需要网格，再通过CASE进行透视。 - 谨慎地将计数转换为比率，使用
100.0和NULLIF避免整数除法和除零。 - 将生成的网格 LEFT JOIN 进来以填充零单元格，并记住矩阵呈三角形。
常见问题解答
「构建留存矩阵」课时是免费的吗？
是的 — 「构建留存矩阵」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Coding Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 Coding Interview Prep 课程共包含 4 节课。
「构建留存矩阵」这节课中我会学到什么？
按用户群组和时间偏移统计活跃用户，形成留存表。 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 Coding Interview Prep 需要有经验吗？
无需任何先前经验。CoddyKit 上的 Coding Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 2 节课，共 4 节。
「构建留存矩阵」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 Coding Interview Prep 课中编写并运行代码吗？
能。每节 Coding Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。