SQL 面試：如何準確計算第 7 日使用者留存率？

題幹與適用情境

現有兩張 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_through

retained 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 總人數能與註冊來源對帳，並且事件最大時間確實涵蓋所宣告的水位。事件量異常和擷取延遲要分開 告警，避免把資料管線缺口誤判為留存下降。