題幹與適用情境
現有兩張 PostgreSQL 資料表：
CREATE TABLE users (
user_id bigint PRIMARY KEY,
signup_at timestamptz NOT NULL,
acquisition_channel text
);
CREATE TABLE events (
event_id bigint PRIMARY KEY,
user_id bigint NOT NULL,
event_at timestamptz NOT NULL,
event_name text NOT NULL
);請統計註冊日期在 2026 年 1 月 1 日至 1 月 31 日之間的使用者，按註冊日期和獲客管道輸出精確的 第 7 日留存率。業務時區是 America/New_York。使用者註冊的本地日曆日是第 0 日；只有在 signupday + 7 這個本地日曆日內至少發生一次 coreaction_completed，才算第 7 日留存。 第 1 至第 6 日、第 8 日或更晚的事件都不符合這個定義。
結果包含 cohortday、acquisitionchannel、cohortsize、retainedusers 和 d7retentionrate。同一使用者在目標日發生多次符合條件的事件，也只能為分子貢獻一次。 完全沒有回訪的使用者仍在分母中。空管道統一歸入 unknown。
假設 datacompletethrough = '2026-02-01 05:00:00+00'。它是不包含右邊界的資料擷取水位， 對應紐約時間 2 月 1 日零點。只有某個註冊 Cohort 的完整第 7 日都在水位之前，才能參與統計。 因此，1 月 24 日註冊使用者的第 7 日是 1 月 31 日，已經成熟；1 月 25 日註冊使用者的第 7 日 是 2 月 1 日，尚未成熟。
這道題適合資料分析師、分析工程師和產品分析職位。核心難點是寫聚合前先對齊 Cohort 粒度、 回訪視窗、業務時區、資料完整性和分群口徑；最後的除法反而很簡單。
面試官考察重點
第一，候選人是否會先確認「第 7 日留存」的定義。聽起來相近的三種指標其實不同：恰好在第 7 日活躍、第 1 至第 7 日內任意一天活躍、從第 7 日起的任意時間活躍。SQL 即使語法完全正確， 也可能回答錯指標。本題要求第一種。
第二，是否守住分母。分母是成熟註冊 Cohort 中的所有合格使用者。如果從事件表出發，或把回訪 事件內連接到註冊使用者，沒有回訪的人會被悄悄刪除，留存率因此虛高。正確順序是先建立 Cohort， 再透過左連接保留它。
第三，是否能控制資料粒度。最終結果是一列一個註冊日期和管道，留存標記則應是一列一個使用者。 原始事件可能包含重試、重複上報，以及同一使用者在目標日的多次有效操作。直接計算事件列得到的 是操作量，不是留存使用者數，所以分子必須在使用者層級去重。
第四，時間口徑是否正確。本地日曆日不一定等於固定的 24 小時；採用日光節約時間的命名時區還會 改變 UTC 偏移。先得到本地註冊日期，再用命名時區的相鄰零點建立每個使用者的目標日區間，才能 直接表達題意。用 eventat = signupat + INTERVAL '7 days' 判斷的是經過時長，不是日曆日留存。
最後，候選人是否具備正式環境判斷。最近的 Cohort 必須擁有完整觀察視窗；目前時間已經過去， 不代表事件管線的資料已完整。高品質回答會使用資料水位，說明延遲事件如何重算，並根據查詢頻率 和資料量選擇索引或使用者日活明細表。
回答前需要釐清的問題
- 第 7 日是精確日、區間還是滾動定義？ 本文只統計註冊後的第七個本地日期。「七日內」以及
「第 7 日及以後」需要不同的事件條件。
- 哪個事件代表留存？ 本題使用
coreactioncompleted。登入、瀏覽、購買或任意事件體現的
產品價值不同，不能隨意替換。
- 一天按什麼時區定義？ 本題統一使用
America/New_York日曆日。改用使用者個人時區或 UTC，
會改變註冊歸屬和回訪視窗。
- Cohort 何時成熟？ 只有不包含右邊界的資料水位達到或超過目標第 7 日之後的本地零點時，
該 Cohort 才能統計。
- 延遲事件怎麼處理？ 水位推進或水位前的資料發生回補後，受影響的 Cohort 必須重算，已顯示
的留存率不應被視為永遠不變。
- 採用哪個管道值？ 查詢假設
users.acquisition_channel是註冊時記錄且不可變的歸因。若欄位
會更新，應使用帶版本的註冊歸因快照。
- Cohort 的聚合粒度是什麼？ 本題按註冊日與管道分組。週 Cohort 的使用者層級邏輯相同，只需
改變最終分組鍵。
- 比率輸出小數還是百分數？ 查詢保留四位小數；
0.3333表示 33.33%。
30 秒回答框架
「我會先把精確第 7 日定義為：使用者註冊本地日期加七，並固定業務時區。第一步用本地零點對應的 UTC 邊界建立 1 月註冊 Cohort，保留每個使用者的註冊日和註冊時管道；第二步排除完整目標日尚未 被不包含右邊界的資料水位涵蓋的 Cohort；第三步在每個使用者的本地第 7 日半開區間中尋找目標 事件，並去重為一名使用者一筆留存記錄；第四步把這個標記左連接回成熟 Cohort，再按日期和管道 統計分母、分子與比率。我會特別測試重複事件、零回訪、第 6 日與第 8 日、本地零點、日光節約 時間和成熟邊界。」
分步深入解答
先把統計參數明確寫出來。cohort_end 不包含右邊界，水位代表第一個尚未完整觀測的時刻。半開 區間能避免零點事件被相鄰兩天重複計算，也方便多個連續日期區間銜接。
WITH params AS (
SELECT
'America/New_York'::text AS tz,
DATE '2026-01-01' AS cohort_start,
DATE '2026-02-01' AS cohort_end,
TIMESTAMPTZ '2026-02-01 05:00:00+00' AS data_complete_through
),
cohort AS (
SELECT
u.user_id,
COALESCE(u.acquisition_channel, 'unknown') AS acquisition_channel,
(u.signup_at AT TIME ZONE p.tz)::date AS signup_day
FROM users AS u
CROSS JOIN params AS p
WHERE u.signup_at >= (p.cohort_start::timestamp AT TIME ZONE p.tz)
AND u.signup_at < (p.cohort_end::timestamp AT TIME ZONE p.tz)
),
mature_cohort AS (
SELECT c.*
FROM cohort AS c
CROSS JOIN params AS p
WHERE c.signup_day + 7
< (p.data_complete_through AT TIME ZONE p.tz)::date
),
retained AS (
SELECT DISTINCT c.user_id
FROM mature_cohort AS c
CROSS JOIN params AS p
JOIN events AS e
ON e.user_id = c.user_id
AND e.event_name = 'core_action_completed'
AND e.event_at >= ((c.signup_day + 7)::timestamp AT TIME ZONE p.tz)
AND e.event_at < ((c.signup_day + 8)::timestamp AT TIME ZONE p.tz)
)
SELECT
c.signup_day AS cohort_day,
c.acquisition_channel,
COUNT(*) AS cohort_size,
COUNT(r.user_id) AS retained_users,
ROUND(
COUNT(r.user_id)::numeric / NULLIF(COUNT(*), 0),
4
) AS d7_retention_rate
FROM mature_cohort AS c
LEFT JOIN retained AS r
ON r.user_id = c.user_id
GROUP BY c.signup_day, c.acquisition_channel
ORDER BY c.signup_day, c.acquisition_channel;cohort 先把 1 月在紐約時區的起止零點轉換成 UTC 時刻，再與 timestamptz 欄位比較。這樣既 準確表達本地日期範圍，又避免在 WHERE 中逐列轉換 signup_at，一般的時間範圍索引仍有機會 生效。
成熟判斷用日期更直觀。水位在紐約時區轉換後是 2 月 1 日；目標日期必須嚴格早於 2 月 1 日， 才代表它的結束零點已被涵蓋。1 月 24 日加七得到 1 月 31 日，可以納入；1 月 25 日加七得到 2 月 1 日，必須排除。若水位不是本地零點，而是當天中午，更穩妥的寫法是直接比較目標日結束 時刻：
((c.signup_day + 8)::timestamp AT TIME ZONE p.tz)
<= p.data_complete_throughretained CTE 產生的是類似半連接的使用者標記。即使事件產生端發生重試，或使用者在目標日 完成十次核心操作，DISTINCT 也保證每個使用者最多一列。在目前資料表結構中，一名使用者只 屬於一個註冊 Cohort，因此按 user_id 連接即可。若同一個人可能多次重新註冊，模型還需要穩定 的註冊週期 ID，後續連接也必須使用該 ID。
最終左連接保留成熟 Cohort 的全部使用者。COUNT(*) 是分母，COUNT(r.user_id) 只統計成功 比對的留存標記。NULLIF 對空分組做防禦；雖然從 mature_cohort 產生的實際分組至少有一列， 但寫清除以零的行為，有助於後續重用時避免意外。
用小型資料驗證：1 月 1 日 organic Cohort 有 3 名使用者，只有 1 人在目標日完成核心操作，結果 應為 3、1、0.3333；1 月 2 日 paid Cohort 有 2 人且都回訪，結果應為 2、2、 1.0000。1 月 25 日註冊使用者不論後來是否有事件，都因水位下尚未成熟而不會出現在結果中。
彙總整體留存率時不能直接平均各列比率。1 人的每日 Cohort 不應與 1000 人的 Cohort 擁有相同 權重。正確公式是：
overall_retention = SUM(retained_users) / SUM(cohort_size)正式環境資料量較大時，可建立與篩選條件和連接鍵相符的索引：
CREATE INDEX users_signup_at_idx
ON users (signup_at);
CREATE INDEX events_core_action_user_time_idx
ON events (user_id, event_at)
WHERE event_name = 'core_action_completed';只有當目標事件定義穩定、查詢價值足以涵蓋寫入和儲存成本時，部分索引才合適。若大型事件流需要 頻繁更新儀表板，可以增量維護一張以 (userid, activityday, event_name) 為唯一鍵的日活明細 表，避免每次掃描原始事件。activity_day 必須沿用相同的命名時區口徑，否則最佳化會悄悄改變 指標。
設合格使用者數為 U，連接實際檢查的相關事件列為 E。Cohort 範圍掃描與使用者數線性相關， 留存去重通常需要對候選記錄做雜湊或排序，最終聚合與成熟使用者數線性相關。真實成本取決於事件 選擇性、索引、統計資訊和執行計畫，因此應在代表性資料上檢視 EXPLAIN (ANALYZE, BUFFERS)， 不能只依 SQL 文字承諾固定複雜度。
高品質示範回答
「寫 SQL 前，我會固定四個口徑：精確第 7 日、以 coreactioncompleted 作為回訪事件、按紐約 日曆日計算，以及使用不包含右邊界的完整性水位。分母是所有完整第 7 日已被觀測的 1 月註冊 使用者；分子是其中至少有一次目標事件的去重使用者。
我會先建立 Cohort。把 1 月在業務時區的起止零點轉換成 UTC 時刻，用它篩選 signup_at，然後 保留每名使用者的本地註冊日和註冊時管道。成熟度單獨放在一個 CTE 中，便於稽核：在本題 2 月 1 日零點的水位下，1 月 24 日是最後一個可以納入的註冊日期。
對每名成熟使用者，我按 user_id、目標事件名稱和目標日半開區間連接事件。區間從 signupday + 7 的本地零點開始，到 signupday + 8 的本地零點之前結束。這兩個邊界由命名 時區計算，所以日光節約時間不會把日曆規則變成固定 168 小時。接著對使用者 ID 去重，把留存 標記左連接回 Cohort，再按註冊日與管道聚合。左連接很重要，因為沒有回訪的使用者仍屬於分母。
驗證時我會逐一檢查 CTE：Cohort 必須一名 1 月註冊使用者一列；成熟 Cohort 在目前水位下必須 停在 1 月 24 日；留存 CTE 的使用者 ID 必須唯一；最終每組都滿足 0 <= retainedusers <= cohortsize。測試資料要涵蓋重複事件、零回訪、錯誤事件名稱、第 6 日 與第 8 日、本地零點兩側、日光節約時間日期、空管道和延遲回補。跨組總留存率用分子總和除以 分母總和，不能平均各組比率。」
常見錯誤
- 使用內連接。 零回訪使用者被刪除，留存率會虛高。應先建 Cohort，再左連接使用者層級留存
標記。
- 計算事件而非使用者。
COUNT(e.event_id)可能大於 Cohort 人數。分子要按使用者去重，或
使用布林存在性判斷。
- 沒有說清楚第 7 日。 涵蓋第 1 至第 7 日或第 7 日之後的條件都在計算其他指標。先用自然
語言寫明區間，再寫 SQL。
- 把註冊時刻加 168 小時。 這是經過時長留存，不是命名時區下的日曆日留存，在日光節約時間
變化附近可能得到不同結果。
- 在篩選條件中轉換被索引時間欄位。 逐列把
signup_at轉成日期可能阻礙範圍掃描。應把本地
常數邊界轉換成時刻。
- 納入未成熟 Cohort。 新使用者還沒有完整回訪機會，會產生虛假的低留存。應使用資料管線
水位，不能只看目前時間。
- 把水位視為永久真相。 回補會改變已統計 Cohort。必須定義重算和資料新鮮度規則。
- 讀取會變化的管道欄位。 目前歸因可能把未來資訊洩漏到歷史 Cohort。應使用不可變註冊欄位
或帶版本快照。
- 平均分組百分比。 Cohort 大小不同，無權平均會扭曲總值。應先彙總人數再相除。
- 忽略空值和缺少資料的語意。 空管道需要明確歸類；發布指標前也要判斷沒有事件究竟代表零
活躍，還是管線資料不完整。
追問及應對
如何計算第 1 至第 7 日內的留存？
保留相同的 Cohort 和成熟判斷，把事件區間改成從 signup_day + 1 的本地零點開始，到 signup_day + 8 的本地零點之前結束。成熟度仍要求整個第 7 日完整。還要明確第 0 日是否納入， 因為不同團隊和分析工具可能採用不同規則。
如何計算「第 7 日及以後」的留存？
下界仍是 signup_day + 7 的本地零點，上界則改成報表截止時間。這個指標是累計型的，會受觀察 時長影響：較舊的 Cohort 擁有更多回訪機會。因此只能在相同 Cohort 年齡下比較，或顯示完整 留存曲線，不能發布一個沒有封頂視窗的數字後直接橫向比較。
PostgreSQL 能否不使用單獨的 retained CTE？
可以。橫向 EXISTS 查詢或設計得當的聚合，都能為每名 Cohort 使用者產生一個布林值。也可直接 連接後使用 COUNT(DISTINCT e.user_id) FILTER (WHERE ...)，但聚合前可能產生大量事件列。單獨 CTE 能把粒度和正確性證明寫得更清楚；最終方案應由實測執行計畫決定。
如果每名使用者採用不同的報告時區怎麼辦？
需要儲存註冊週期適用的時區，並用相同值推導 Cohort 日期和目標日邊界。使用者歷史時區變更也 需要明確政策。個人時區還表示同一個日曆 Cohort 標籤會跨越不同 UTC 區間，預先聚合的資料必須 保留適用時區，或直接儲存已標準化的本地日期。
如何測試成熟邊界？
在目前水位下分別建立 1 月 24 日和 1 月 25 日的註冊使用者，並都加入合法目標日事件。1 月 24 日必須出現，1 月 25 日必須缺席。再把水位設成目標日結束零點前一秒和恰好等於零點，確認不包含 右邊界的規則沒有多收或少收。
延遲事件應該如何處理？
對外公布事件時間水位，並重算所有合格事件區間與回補範圍重疊的 Cohort。如果擷取延遲有明確 服務目標，可以在名義第 7 日結束後增加安全延遲。保留原始人數與指標版本，確保每次修正可追蹤。
正式環境應監控哪些不變量？
檢查每組 cohort_size 都大於零、留存人數介於零和 Cohort 人數之間、不存在未成熟日期、Cohort 總人數能與註冊來源對帳，並且事件最大時間確實涵蓋所宣告的水位。事件量異常和擷取延遲要分開 告警，避免把資料管線缺口誤判為留存下降。