題目與適用情境
現有一張 PostgreSQL 事件資料表：
CREATE TABLE user_logins (
user_id bigint NOT NULL,
login_at timestamptz NOT NULL
);對每位使用者，找出其在 America/New_York 當地日曆日內至少登入一次的最長連續日期區間， 回傳 userid、streakstart、streakend 與 streakdays。同一當地日期內的多筆事件只算 一個活躍日；少了任何一個當地日期就中斷區間；如果某位使用者有兩段長度相同的最長區間， 兩筆都要回傳。沒有事件的使用者不會出現在結果中。
這是一道資料與 SQL 面試題，計算的不是連續數個 24 小時。日光節約時間切換附近的當地日曆日 可能只有 23 小時或長達 25 小時，但依然只是日曆上的一天。因此，題目要求先固定報表時區， 再把時間戳記轉換成日期。主要解法使用 PostgreSQL，其他資料庫方言的日期運算寫法會有所不同。
核心是 gaps and islands 問題：對排序後的日期做轉換，讓同一段連續日期共用一個穩定鍵，依該鍵 彙總出每個連續區間，最後為每位使用者保留所有並列最長的區間。
面試官在評估什麼
第一個訊號是應徵者能否在寫視窗函式前先確定粒度。原始資料一列代表一次登入事件，業務粒度 卻是「每位使用者、每個當地日曆日一列」。如果省略這一步，同一天的重複事件會錯誤推進 ROW_NUMBER()，也會放大計數並改變區間邊界。
第二個訊號是能否推導連續區間的分組鍵。去重後的日期排好順序，在一段連續日期內，日期與 ROW_NUMBER() 都逐列增加 1。因此，用日期減去列號對應的天數位移，同一連續區間內會得到 相同的值。遇到缺口時，日期增加超過 1 天，列號仍只增加 1，分組鍵便會改變。
第三個訊號是對輸出契約的掌握。「最長連續登入」在存在等長區間時會有歧義。用 ROW_NUMBER() 只挑一筆，會悄悄遺漏合法的並列結果。本題明確要求回傳全部並列最長區間， 所以需要把每個區間長度與該使用者的最大區間長度比較。
第四個訊號是時區正確性。直接把 login_at 轉成 date 會採用資料庫工作階段時區，不同環境 可能得到不同答案。正確做法是先把 timestamptz 轉到指定業務時區，再取日期。對於會隨 日光節約時間改變偏移量的地區，固定 UTC 偏移量並不足夠。
最後還要看驗證與規模判斷。正確查詢應逐層檢查每個 CTE，並涵蓋同日重複事件、單日區間、 日期缺口、並列最長、當地午夜與日光節約時間邊界。若要反覆查詢一張很大的事件資料表，把原始 事件預先縮減成每位使用者每天一列，通常比微調最後的視窗查詢更有價值。
回答前要先確認的問題
- 一天如何定義？ 命名業務時區、UTC 或每位使用者自己的時區會改變日期轉換，甚至改變
最終答案。本題統一使用 America/New_York。
- 同一天登入多次是否重複計數？ 本題不重複計數，所以必須在編號前去重。如果指標計算的是
連續事件，粒度與分組規則都會改變。
- 少一天是否一定中斷？ 是。如果題目改成 30 分鐘沒有活動才劃分新工作階段，就應比較
前一列，不能再用嚴格的日曆日相鄰關係。
- 並列結果如何處理？ 本題回傳全部並列最長區間。如果只取最近區間，需要另外明確訂出
同分排序規則。
- 時間範圍是否有限制？ 日期篩選可以減少工作量，但也可能截斷從範圍起點之前開始的
連續區間。呼叫端必須說明要的是「範圍內連續區間」或跨越邊界的完整區間。
userid或loginat是否可能為空？ 目前資料表結構規定不可為空。如果允許空值，
就要在排序與分組前約定處理方式。
- 這是一次性查詢或長期產品指標？ 臨時分析可以直接掃描並排序每日資料。持續更新的
儀表板可能值得維護一張增量更新的使用者日資料表。
30 秒回答架構
「我會先依約定的業務時區轉換 timestamptz，並去重成每位使用者、每個當地日期一列。接著在 每位使用者內依日期排序並產生 ROWNUMBER()。對嚴格連續的日期，loginday - 列號 × 1 天 在同一連續區間內保持不變，遇到缺口就會改變，因此可以依這個鍵彙總出區間起訖與長度。然後 把每個區間長度與該使用者的最大長度比較，這樣不會遺漏並列結果。我會測試同日重複事件、單日 區間、日期缺口、並列最長、當地午夜與日光節約時間邊界，並在具代表性的資料上檢查執行計畫。」
分步深入分析
先把事件流正規化到業務粒度。對 timestamptz 使用命名時區執行 AT TIME ZONE，會得到該時區的 當地時鐘時間；再轉成 date，便得到業務日曆日。SELECT DISTINCT 隨後保證每位使用者每天恰好 一列。
完整查詢如下：
WITH login_days AS (
SELECT DISTINCT
user_id,
(login_at AT TIME ZONE 'America/New_York')::date AS login_day
FROM user_logins
),
numbered AS (
SELECT
user_id,
login_day,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY login_day
) AS rn
FROM login_days
),
grouped AS (
SELECT
user_id,
login_day,
login_day - (rn * INTERVAL '1 day') AS island_key
FROM numbered
),
streaks AS (
SELECT
user_id,
MIN(login_day) AS streak_start,
MAX(login_day) AS streak_end,
COUNT(*) AS streak_days
FROM grouped
GROUP BY user_id, island_key
),
scored AS (
SELECT
user_id,
streak_start,
streak_end,
streak_days,
MAX(streak_days) OVER (PARTITION BY user_id) AS max_streak_days
FROM streaks
)
SELECT
user_id,
streak_start,
streak_end,
streak_days
FROM scored
WHERE streak_days = max_streak_days
ORDER BY user_id, streak_start;證明可以直接沿著每日排序資料展開。對某位使用者，把去重日期記為 d1, d2, ...，對應列號為 1, 2, ...。若 d(i+1) = d(i) + 1 天，下一列日期與列號各多 1，減去列號天數位移後得到的 分組鍵不變。若中間至少少了一天，d(i+1) 會增加 2 天或更多，而列號仍只增加 1，分組鍵變大， 新的區間便從此開始。由於已經去重，COUNT(*) 等於日曆日數量，MIN 與 MAX 則正好是區間邊界。
最後一步特意使用視窗 MAX，沒有再用 ROW_NUMBER()。只要區間長度等於該使用者的最大長度， 它就會保留下來。如果產品後來只想要一筆紀錄，應先寫清楚「結束日期最近者優先」之類的規則， 再採用確定性排序；不能在 SQL 裡自行補出這項規則。
假設兩位使用者正規化後的日期如下：
user 1: Mar 07, Mar 08, Mar 09, Mar 11, Mar 12
user 2: Nov 01, Nov 02, Nov 04, Nov 05
result:
user 1 | Mar 07 | Mar 09 | 3
user 2 | Nov 01 | Nov 02 | 2
user 2 | Nov 04 | Nov 05 | 2使用者 1 的最大區間為 3 天。使用者 2 有兩個獨立的 2 天最大區間，所以必須回傳兩筆。同一顯示 日期上無論有多少筆原始事件，結果都不變。日光節約時間切換附近，應判斷當地日期是否相鄰， 不能判斷兩個時間戳記是否剛好相隔 24 小時。
設原始事件數為 N，去重後的使用者日紀錄數為 D。去重需要讀取 N 列，可能使用雜湊或排序； 視窗步驟最多依使用者與日期排序 D 列。面試中可給出一個實用的排序計畫上界：時間複雜度 O(N log N + D log D)，中間空間 O(D)，同時說明最佳化器也可能使用雜湊、既有順序、平行執行 或磁碟溢寫。正式環境能否接受，最後要看執行計畫，不能只看 Big-O。
如果要反覆在數十億筆事件上計算指標，可以增量維護一張以 (userid, loginday) 為唯一鍵的 資料表。如此一來，時區轉換與同日去重會移到資料匯入或批次處理邊界，連續區間查詢只需讀取 D 筆每日紀錄，不必重複掃描 N 筆事件。如果臨時查詢限制了時間範圍，應先用可使用索引的 UTC 時間戳記邊界篩選，再轉成當地日期；這些 UTC 邊界要由命名時區的當地午夜推導，才能正確 處理日光節約時間變化。
不要只看最終結果，也要檢查中間層：
-- These checks are run against the corresponding CTE or materialized test result.
SELECT user_id, login_day, COUNT(*)
FROM login_days
GROUP BY user_id, login_day
HAVING COUNT(*) > 1;
SELECT *
FROM numbered
ORDER BY user_id, login_day;
SELECT *
FROM streaks
WHERE streak_days <> (streak_end - streak_start + 1);第一項與第三項檢查都應回傳零列。numbered 的輸出能直接顯示粒度或排序錯誤。把完整查詢加上 EXPLAIN (ANALYZE, BUFFERS) 前綴，可以看到掃描、排序、列數估算、暫存 I/O，以及是否需要更早 縮減原始事件。如果直接執行正式環境語句的成本過高，應在安全且具代表性的資料副本上執行。
日期平移法並非通用解。如果超過 30 分鐘才劃分新工作階段，或狀態值改變才開啟新區間，應使用 LAG() 查看前一列，標記每個邊界，再對邊界標記做累計 SUM()。選擇規則很直接：嚴格按固定 單位連續時用平移鍵；連續性取決於自訂比較時用邊界標記。
高品質示範回答
「寫 SQL 前，我會先確定粒度與並列結果契約。原表中每位使用者可能有多筆事件，但指標按每位 使用者在 America/New_York 的一個日曆日計一次。因此，我會先把 timestamptz 轉成該命名 時區，再取 date 並去重，之後才使用視窗函式。這也能避免工作階段時區悄悄改變答案。
處理 gaps and islands 時，我會在每位使用者內依當地日期排序並產生 ROW_NUMBER()。連續區間 內，日期與列號都逐列加 1，所以用日期減去列號對應的天數位移會得到常數。若少了日期，日期的 跳幅會大於列號，分組鍵便會改變。依使用者與這個鍵彙總，就能得到每段連續區間的開始、結束與 活躍日數。
接著我會對區間長度計算視窗最大值，並保留與最大值相等的所有區間。這樣符合題目要求，不會 悄悄遺漏並列最長紀錄。排序型計畫的上界約為 O(N log N + D log D)，其中 N 是原始事件數， D 是去重使用者日數；實際是否溢寫仍要看執行計畫。
測試資料會涵蓋同日多筆事件、只有一天的使用者、內部日期缺口、兩個並列最大區間、當地午夜 兩側的事件與日光節約時間切換。對長期運作的大規模指標，我會維護唯一使用者日資料表，在較小 粒度上執行視窗邏輯。如果連續定義從日曆日相鄰改為時間門檻，我會換成 LAG()、邊界標記與 累計和。」
常見錯誤
- 直接替原始事件編號 → 同日重複事件會推進列號並放大計數 → **先去重成每位使用者每天一列，
再使用視窗函式。**
- 直接把
timestamptz轉成date→ 答案取決於工作階段時區 → 先轉換到指定業務時區。 - 使用固定 UTC 偏移量 → 命名時區改變偏移量時，當地日期會算錯 → **使用帶有日曆規則的
IANA 時區。**
- 比較時間戳記是否相隔 24 小時 → 23 小時或 25 小時的當地日曆日會中斷合法區間 → **依契約
比較當地日期是否相鄰。**
- 只依平移後的日期分組 → 分組鍵相同的不同使用者會合併 → **同時依
user_id與
island_key 分組。**
- 用
ROW_NUMBER()只取一筆 → 並列最長區間會被遺漏 → 把每個區間與該使用者最大值比較。 - 沒有邊界規則就篩選報表區間 → 跨越起始日的區間遭截斷並可能被誤標 → **明確定義結果是
範圍內區間或完整區間。**
- 使用
LAG()時不處理第一列 → 第一段區間沒有起點 → 把前一列為空視為新分組開始。 - 只說 Big-O → 排序溢寫或基數估算錯誤仍然看不見 → **檢查中間列數與
EXPLAIN (ANALYZE, BUFFERS)。**
- 每次更新儀表板都掃描完整事件歷史 → 重複的時區轉換與去重占用主要成本 → **工作負載足夠大時
維護唯一使用者日粒度。**
追問與回答
追問 1：如果只回傳最近的一段最長區間，怎麼改？
連續區間的建構方式不變。取得 streaks 後，依使用者依序使用 streak_days DESC、 streakend DESC，最後以 streakstart DESC 做確定性的最終同分排序，只回傳排名第一。要明確 說明輸出契約已經改變：等長區間不再全部保留。
追問 2：如果 30 分鐘沒有活動就結束一次工作階段，怎麼改？
日期平移無法表達這種連續性。依使用者與時間戳記排序，使用 LAG(login_at) 取得前一筆紀錄； 第一列或間隔超過 30 分鐘時標記為新工作階段，再用明確的 ROWS UNBOUNDED PRECEDING 視窗框架 對標記做累計 SUM。最後依使用者與產生的工作階段 ID 彙總。
追問 3：如果每位使用者都有自己的時區，怎麼處理？
把事件關聯到事件發生時有效的版本化使用者時區，再取日期。只使用個人資料中的目前時區， 使用者搬家後會改寫歷史日期。需要先確認產品希望歷史活動固定在當時的時區，或依使用者目前 時區重新計算；這是兩個不同的指標。
追問 4：如果只查最近 90 個當地日曆日，怎麼做？
先明確定義區間是否允許從視窗之前開始。若只要視窗內結果，依命名時區的起訖當地午夜推導兩個 UTC 時刻，用它們篩選 login_at，再進行正規化。若要完整區間，就必須額外讀取足夠早的每日紀錄， 直到找到真正缺口；直接截取 90 天無法證明真實起點。
追問 5：如何支援每日儀表板的高效率查詢？
維護 userlogindays(userid, loginday)，設定唯一鍵並使用冪等 upsert。事件流程依統一時區規則 更新該表。只重算每日紀錄有變化的使用者，或定期從帶有重疊的時間視窗重建，以吸收延遲到達的 事件。發布指標前，把每日列數與原始資料來源核對。
追問 6：上線前必須有哪些測試？
使用表格驅動測試案例涵蓋重複事件、單列使用者、內部缺口、並列最長、當地午夜、日光節約時間 開始與結束、延遲事件及報表邊界穿越。斷言每日紀錄唯一，每段區間都符合 streakdays = streakend - streak_start + 1，而且所有回傳區間都等於該使用者最大值。抽樣 使用者要把增量每日資料表與原始事件重新計算結果比較，最後在接近正式環境的基數上檢查查詢 計畫與暫存 I/O。