題目與適用情境
分析資料表為 events(eventid, userid, eventat, eventname, isinternaluser)，其中 eventat 是 PostgreSQL 的 timestamptz。使用者至少發生一次 appopen、 viewdashboard 或 runreport 才算活躍，內部使用者不計入。
依 America/New_York 日曆日返回 2026 年 6 月 1 日至 6 月 30 日的每一天。報告日的 rolling7dactive_users 是當天或此前六個日曆日內活躍過的不同合格使用者數。因此 6 月 1 日 需要讀取 5 月 26 日至 6 月 1 日；一位使用者在三天產生二十筆事件，視窗內仍只計一次。
查詢必須保留零使用者日期，明確時間邊界，並說明延遲事件如何改變已發布結果。核心是滾動集合的 去重聯集，不能把已經彙總的每日人數再做滾動加總。
面試官考察什麼
第一是先定義指標再寫語法。強回答會說明合格事件、排除族群、報告時區、輸出粒度、包含首尾的 七個日期視窗和資料完整性邊界。缺少這些定義時，兩條語法正確的 SQL 可能回答不同問題。
第二是粒度控制。原始事件先變成唯一的 (userid, activitydate)。這一步消除同日重複，但 有意保留使用者在不同日期的記錄。最後一個視窗再統計這些使用者集合的去重聯集。
第三是能否拒絕看似方便的捷徑。七個 DAU 相加會讓同一使用者依活躍天數重複計數。 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 表示七列，不保證是七個日曆日；即使作用於完整 每日人數，它也無法恢復跨日使用者聯集。
最後是正式環境判斷：請求範圍之前要多掃描六天，用日期脊柱保留空日期，依宣告時區轉換時間戳， 定義延遲事件的重新整理語意，並有意識地選擇精確或近似擴充方案。
回答前需要釐清的問題
- 什麼事件算活躍？ 只登入與發生有意義產品行為得到的集合不同。事件名、機器人和內部使用者
排除都屬於指標契約。
- 哪個時區定義一天？ 本題使用
America/New_York。UTC 會把本地午夜附近的事件放到另一日。 - 視窗是七個日曆日還是連續 168 小時？ 本題要求本地日曆日。日光節約時間切換附近的七天
可能只有 167 或 169 個小時。
- 兩端是否包含？ 使用者集合涵蓋
reportdate - 6到reportdate。來源時間戳過濾使用
左閉右開區間，避免把下一次午夜重複計入。
- 沒有事件的日期是否也要輸出？ 要。應產生三十個報告日期，不能只從事件表取日期。
- 事件表何時完整？ 如果事件最多延遲三天，近期結果只能標記為暫定，或附帶水位。SQL 無法
把不完整輸入變成最終結果。
- 必須精確去重嗎？ 面試題要求精確。只有誤差契約獲得認可後，超大規模才可使用可合併的
近似集合表示。
- 資料庫和資料量是什麼？ 本文使用 PostgreSQL。資料倉儲可以替換日期產生或點陣圖函式，
但集合語意不變。
30 秒回答框架
「我先定義活躍事件、排除族群、America/New_York 日期邊界和每天一列的輸出粒度。6 月 1 日 需要此前六天，所以來源資料從 5 月 26 日開始掃描；把時間戳轉為本地日期後，先去重成每位使用者 每天一列。接著用 generate_series 產生 6 月 1 日至 30 日的日期脊柱，讓每個報告日左連接 date - 6 至當天的活躍記錄，再統計不同使用者。
我不會加總 DAU，因為跨多日活躍的使用者會重複；前六列視窗也會受缺失日期影響，而且不會建立 跨日去重聯集。驗證會涵蓋午夜、重複事件、空日期和預熱區間；延遲事件則透過水位標示或重新整理 近期日期處理。」
分步驟深入解答
第一步：固定指標契約與輸入範圍
輸出從 6 月 1 日開始，但來源掃描從 5 月 26 日開始。只讀取 6 月會低估前六個報告日。來源上界 為 7 月 1 日本地午夜，6 月 30 日的尾隨視窗不需要未來事件。
先把這些本地午夜寫成 timestamptz 常數，並直接過濾有索引的 event_at。若在 WHERE 裡把每個來源時間戳都轉成日期，普通範圍索引可能無法有效裁剪。
第二步：正規化成去重使用者日
先用有界時間戳過濾，再把合格時刻轉換為紐約日曆日，最後依 user_id 和本地日期分組。一次事件 重試即使產生不同事件列，一位使用者同日產生二十筆事件，也都會縮成一個使用者日。
這一步並未直接解決最終問題。同一使用者若 6 月 1 日和 2 日都活躍，兩筆使用者日仍要保留，才能 讓不同報告日的尾隨視窗正確包含該使用者。
第三步：產生完整日期脊柱
generate_series 在沒有事件時仍產生每個報告日。若從事件表取日期，空日期會消失，ROWS 視窗的列數也會偏離日曆，而且儀表板上不會出現值為零的點。日期脊柱才是權威輸出粒度。
第四步：統計每個視窗的去重聯集
直接、精確的查詢如下：
WITH params AS (
SELECT
DATE '2026-06-01' AS report_start,
DATE '2026-06-30' AS report_end
),
activity_days AS (
SELECT
e.user_id,
(e.event_at AT TIME ZONE 'America/New_York')::date AS activity_date
FROM events AS e
WHERE e.event_at >= TIMESTAMPTZ '2026-05-26 00:00:00 America/New_York'
AND e.event_at < TIMESTAMPTZ '2026-07-01 00:00:00 America/New_York'
AND e.event_name IN ('app_open', 'view_dashboard', 'run_report')
AND e.is_internal_user = false
GROUP BY
e.user_id,
(e.event_at AT TIME ZONE 'America/New_York')::date
),
report_dates AS (
SELECT gs::date AS report_date
FROM params AS p
CROSS JOIN generate_series(
p.report_start,
p.report_end,
INTERVAL '1 day'
) AS gs
)
SELECT
d.report_date,
COUNT(DISTINCT a.user_id) AS rolling_7d_active_users
FROM report_dates AS d
LEFT JOIN activity_days AS a
ON a.activity_date BETWEEN d.report_date - 6 AND d.report_date
GROUP BY d.report_date
ORDER BY d.report_date;左連接保留沒有符合活動的報告日，COUNT(DISTINCT a.user_id) 會忽略左連接產生的空值。區間 恰好包含七個 date：當天和此前六天。
第五步：證明常見視窗捷徑為什麼失敗
假設使用者 A 週一和週二都活躍，使用者 B 只在週二活躍。兩日 DAU 分別是 1 和 2，但兩日使用者 聯集是 2，不是 3。每日人數一旦取代使用者身分，SQL 就無法得知 A 在兩天重複。
列視窗還有另一層錯誤。若週三無事件且輸入中沒有週三，「前六列」可能向前涵蓋八個甚至更多 日曆日。日期脊柱能修復日曆缺口，但對 DAU 做滾動加總仍會重複使用者。正確順序是先求聯集，再 求基數。
第六步：在不改變語意的前提下擴充
中等範圍下，為原始資料建立 event_at 索引，並在 7 倍區間連接前縮減成使用者日。依 (activitydate, userid) 儲存具體化日活明細，可避免反覆掃描原始事件；分割區裁剪必須包含 預熱日。
報告範圍較長時，可把每個使用者日展開到最多七個適用報告日，限制在請求範圍內後再依使用者去重。 連接形態會改變，但最差仍是 7 倍展開。支援精確點陣圖的引擎可以合併每日使用者點陣圖；近似 sketch 必須支援集合聯集並公開實測誤差。直接相加每日 HyperLogLog 估計值仍然錯誤，因為基數 相加不能消除交集。
第七步：定義延遲資料與驗證
結果應附帶 as_of 水位。如果管線允許事件延遲三天，至少要重新整理所有七日輸入視窗與可變資料 相交的報告日。穩定事件 ID 有助於擷取去重，使用者日分組能抵抗同一使用者的多筆合格事件；兩者 都不能取代完整性監控。
手工建立對照資料：重複事件、同一使用者跨多日、內部使用者、不合格事件、5 月 26 日和 31 日的 預熱事件、空日期、本地午夜兩側的時刻，以及日光節約時間邊界。用簡單應用集合聯集逐日對照。再 在接近正式環境的資料量上執行 EXPLAIN (ANALYZE, BUFFERS)，檢查來源裁剪、使用者日基數、 連接展開、耗時與寫入磁碟。
高品質示範回答
「我會先定義集合：合格使用者至少產生一次允許的產品事件，排除內部使用者，一天依 America/New_York 計算。對報告日 D，集合包含本地活動日期在 D 減六至 D 之間的所有合格 使用者，首尾都包含；輸出必須有三十列。
我會用左閉右開的本地午夜邊界過濾 5 月 26 日至 7 月 1 日的原始時間戳，過濾後再轉換本地日期， 依使用者和日期分組。generate_series 產生 6 月 1 日至 30 日，每個日期左連接其尾隨區間中的 使用者日，再用 COUNT(DISTINCT user_id) 得到聯集基數。
我會拒絕 SUM(DAU)，因為跨多日使用者會重複；也拒絕 ROWS 6 PRECEDING，因為列不等於 日曆日，而且每日彙總已經遺失身分。擴充時先具體化 (activitydate, userid) 並裁剪預熱範圍； 只有精確值不再是要求時，才考慮精確點陣圖聯集或經過誤差驗證的近似集合聯集。結果附水位，延遲 資料觸發有界重算。」
常見錯誤
- 加總七個 DAU → 重複使用者會依活躍日期重複計數 → 在視窗中合併使用者身分後再計數。
- 在稀疏日期上使用
ROWS 6 PRECEDING→ 六列可能跨越超過六個此前日期 → **產生完整日期
脊柱，明確日曆邊界。**
- 只掃描 6 月 → 6 月初視窗遺失 5 月活動 → 加入六天預熱區間。
- 在來源過濾中轉換
event_at→ 普通時間戳索引可能無法有效裁剪 → **先用左閉右開的
timestamptz 邊界過濾，再衍生本地日期。**
- 統計原始事件 → 重試與重複使用會放大人數 → 先去重使用者日，最終視窗仍依使用者去重。
- 從事件衍生輸出日期 → 空日期會消失 → 讓日期脊柱決定輸出粒度。
- 把近期數字稱為最終值 → 延遲事件會改變集合 → 發布水位並重新整理受影響視窗。
- 相加每日近似基數 → 基數相加無法消除重疊 → 先合併支援集合的 sketch 或點陣圖，再估算聯集。
追問及應對
追問一：如果指標定義為此前連續 168 小時，怎麼改？
改為比較時刻，而不是本地 date。對每個報告時刻使用明確的半開或閉合時間戳區間，例如 (reportat - interval '168 hours', reportat]。日光節約時間切換附近，它與紐約七個日曆日 不同，不能把兩個定義混稱。
追問二：視窗函式本身能完成精確滾動去重嗎？
當聚合可以由列值組合時，視窗聚合很合適，例如滾動加總。本題需要維護集合，並在舊日期離開 視窗時刪除成員。直接 PostgreSQL 解法保留使用者身分，再連接報告日。專用引擎可能提供精確 點陣圖聯集視窗，但那是引擎能力，不等於可以加總每日人數。
追問三：一筆事件延遲三天時，重新整理哪些日期？
先得到事件的本地活動日期 A。它可能影響 A 至 A 加六的報告日，再與已發布範圍求交。只替換這些 分割區，完成對帳後推進水位，並保證重算具備冪等性。只重新整理 A 會漏掉後續六個視窗。
追問四：如果還要依國家分組呢？
先說明國家來自事件、使用者目前資料，還是活動發生時的緩慢變更資料。選擇會改變歷史事實。把 選定國家加入使用者日粒度、日期脊柱組合、分組和驗證對照。若連接目前資料，使用者搬家後歷史 可能被改寫。
追問五：精確去重成本過高怎麼辦？
先測量精確查詢。若核准的誤差預算允許近似，可依活動日期與切片保存可合併集合 sketch，合併 七個 sketch 後只估算一次。用低、中、高基數切片與精確集合比較偏差和相對誤差。計費、資格等 不能容忍估算誤差的決策仍保留精確處理。