題目與適用場景
有一張 PostgreSQL 事件表：
CREATE TABLE events (
user_id bigint NOT NULL,
event_id bigint PRIMARY KEY,
event_name text NOT NULL,
event_time timestamptz NOT NULL
);請計算三步漏斗：visit → signup → purchase。只要使用者在半開報告區間 [startat, endat) 內至少發生一次 visit，就進入群組；區間內最早的一次造訪是漏斗錨點。 合格的註冊是錨點之後最早的註冊，合格的購買是被選中註冊之後最早的購買。兩步都不得晚於錨點 造訪後的 24 小時。
eventid 全域唯一。事件產生端保證：同一使用者的兩個事件時間相同時，較小的 eventid 先發生。 恰好在錨點後 24 小時發生的購買計入，晚一微秒也不計入。漏斗步驟之間允許夾雜其他事件；同一步驟 重複發生，也不能讓一位使用者被重複計數。
回傳一列：visitedusers、signedupusers、purchasedusers、signup_rate 和 purchase_rate。兩個轉換率都以造訪群組為分母，回傳四位小數的比例值。空群組的三個計數為 0， 兩個轉換率為 null。
這道題適用於資料分析師、分析工程師和產品分析職位。它考察的不只是條件聚合，還包括群組粒度、 事件順序、重複事件下的有效鏈選擇、時間邊界，以及如何證明查詢不會把不可能的使用者路徑算成轉換。
面試官在考察什麼
第一個訊號是指標口徑意識。「三個事件都出現過」還不足以定義漏斗。候選人需要問清楚哪次造訪是 錨點、步驟是否必須有序、視窗是在造訪後 24 小時關閉還是每一步重新計時，以及轉換率以前一步還是 初始群組為分母。任何一個選擇改變，結果都會改變。
第二個訊號是粒度控制。原表是一列一個事件，輸出統計的是使用者。群組必須至多一列一位使用者， 後續每次查找也必須至多為這列群組選擇一個事件。先做寬表連接再用 COUNT(DISTINCT user_id)，可能 只是掩蓋錯誤的事件組合，並沒有證明事件鏈有效。
第三個訊號是順序推理。彼此獨立的 MIN(CASE WHEN event_name = 'purchase' ...) 不一定會找到 被選中註冊之後最早的購買。使用者可能先購買、再註冊、隨後再次購買；有效購買是第二次。每一步都 必須依賴上一步實際選中的事件。
第四個訊號是確定性的時間語意。僅靠時間戳無法排列同一時刻的事件。本題明確給出 event_id 作為 平手裁決，因此比較鍵是 (eventtime, eventid)。如果資料來源沒有這項保證，SQL 無法判斷同一 時刻誰先發生，也不應自行編造因果順序。
最後一個訊號是生產判斷。漏斗視窗會延伸到 end_at 之後，因此還需要讀取更晚的事件。只有資料管線 已完整處理到 end_at + 24 小時，最新群組才成熟。高品質回答還會討論延遲資料、索引、執行計畫和 專門攻擊指標定義的測試資料，而不會停在語法正確。
作答前需要釐清的問題
- 哪一個事件作為使用者錨點？ 本文使用報告區間內最早的造訪，即使該使用者在區間前也造訪過。
「歷史首次造訪」需要完整歷史資料和另一種群組篩選。
- 漏斗是否有序？ 是。註冊必須在錨點造訪後，購買必須在被選中的註冊後。只檢查無序事件是否
存在，計算的是另一項指標。
- 視窗從哪裡開始、在哪裡結束？ 從錨點造訪開始，包含
visit_time + 24 hours這一時刻；
註冊後不會重新開始計時。
- 後續步驟能否晚於
endat？ 可以，只要仍在該使用者的 24 小時視窗內。endat只篩錨點
造訪，不應截短轉換機會。
- 同一時間戳怎麼排序？ 先比較
eventtime，再比較eventid，依賴題目明確的產生端保證。
沒有可靠序列時，同一時刻的步驟順序就是未知。
- 轉換率分母是什麼？ 兩個比例都用
visited_users。如果購買率用註冊使用者數作分母，那是步驟間
轉換率，欄位名稱和口徑都應另行說明。
- 空輸入回傳什麼？ 計數為 0；分母不存在，所以比例為 null。用
NULLIF防止除以零。 - 報告何時最終穩定？ 完整性水位必須覆蓋所有群組使用者的 24 小時機會視窗，同時還要執行約定的
延遲資料處理策略。
30 秒回答框架
「我會先把群組整理成每位使用者一列：篩選半開報告區間內的造訪，按時間和事件 ID 排序，保留第一筆。 對每個錨點，用一次左側橫向連接查找在造訪之後且 24 小時內最早的註冊；再用第二次橫向連接，從被 選中的註冊之後查找最早購買，同時仍使用原造訪的 24 小時截止點。這樣每位造訪使用者只產生一列進度。 最後用帶篩選條件的計數統計到達註冊和購買的使用者，並都除以造訪人數。我會測試事件倒序、重複事件、 相同時間戳、24 小時邊界、重複造訪、空群組和報告成熟度。」
分步深入解析
先建構群組。ROW_NUMBER() 之前就完成區間篩選，表達的正是「報告區間內最早的造訪」。視窗排序加入 event_id，可以在多次造訪時間相同時穩定選出錨點。
WITH ranked_visits AS (
SELECT
user_id,
event_id AS visit_event_id,
event_time AS visit_time,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY event_time, event_id
) AS visit_rank
FROM events
WHERE event_name = 'visit'
AND event_time >= :start_at
AND event_time < :end_at
),
cohort AS (
SELECT user_id, visit_event_id, visit_time
FROM ranked_visits
WHERE visit_rank = 1
)
SELECT *
FROM cohort;完整查詢使用兩個相互依賴的橫向子查詢。PostgreSQL 會用左側列的值計算 LATERAL 子查詢。 LEFT JOIN LATERAL 在沒有合格事件時仍保留群組列，這正是漏斗需要的行為：從未註冊的造訪使用者仍在 分母中。
WITH ranked_visits AS (
SELECT
user_id,
event_id AS visit_event_id,
event_time AS visit_time,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY event_time, event_id
) AS visit_rank
FROM events
WHERE event_name = 'visit'
AND event_time >= :start_at
AND event_time < :end_at
),
cohort AS (
SELECT user_id, visit_event_id, visit_time
FROM ranked_visits
WHERE visit_rank = 1
),
progress AS (
SELECT
c.user_id,
c.visit_time,
s.signup_time,
p.purchase_time
FROM cohort AS c
LEFT JOIN LATERAL (
SELECT
e.event_id AS signup_event_id,
e.event_time AS signup_time
FROM events AS e
WHERE e.user_id = c.user_id
AND e.event_name = 'signup'
AND (
e.event_time > c.visit_time
OR (
e.event_time = c.visit_time
AND e.event_id > c.visit_event_id
)
)
AND e.event_time <= c.visit_time + INTERVAL '24 hours'
ORDER BY e.event_time, e.event_id
LIMIT 1
) AS s ON TRUE
LEFT JOIN LATERAL (
SELECT e.event_time AS purchase_time
FROM events AS e
WHERE e.user_id = c.user_id
AND e.event_name = 'purchase'
AND s.signup_time IS NOT NULL
AND (
e.event_time > s.signup_time
OR (
e.event_time = s.signup_time
AND e.event_id > s.signup_event_id
)
)
AND e.event_time <= c.visit_time + INTERVAL '24 hours'
ORDER BY e.event_time, e.event_id
LIMIT 1
) AS p ON TRUE
)
SELECT
COUNT(*) AS visited_users,
COUNT(*) FILTER (WHERE signup_time IS NOT NULL) AS signed_up_users,
COUNT(*) FILTER (WHERE purchase_time IS NOT NULL) AS purchased_users,
ROUND(
COUNT(*) FILTER (WHERE signup_time IS NOT NULL)::numeric
/ NULLIF(COUNT(*), 0),
4
) AS signup_rate,
ROUND(
COUNT(*) FILTER (WHERE purchase_time IS NOT NULL)::numeric
/ NULLIF(COUNT(*), 0),
4
) AS purchase_rate
FROM progress;註冊查找同時證明三件事：事件屬於同一使用者、元組順序嚴格晚於錨點、時間位於錨點的 24 小時視窗內。 排序加 LIMIT 1 會選出唯一且穩定的事件。購買查找依賴已選中的註冊，因此會忽略註冊前的購買，同時 仍能找到後來的有效購買。它的截止時間繼續引用 c.visit_time；若改為從註冊起算，查詢就會意外給出 最多 48 小時。
對固定三步有序漏斗，選擇最早合格事件的貪心策略是正確的。選擇最早註冊不會排除「選更晚註冊時才 能配對」的購買：任何晚於更晚註冊的購買，也一定晚於更早註冊，而且共同截止點沒有變化。選擇最早 合格購買同理。每次查找後的不變量是：已選鏈條有效，並為剩餘步驟留下盡可能長的視窗後綴。
progress 每位群組使用者恰好一列，因為每個橫向子查詢至多回傳一列。帶篩選條件的聚合因此直接數的 就是使用者，無需 DISTINCT。結果還應滿足 purchasedusers <= signedupusers <= visitedusers， 這是一組很實用的結果級斷言。
應避開這類看似簡潔的獨立最小值：
MIN(CASE WHEN event_name = 'signup' THEN event_time END),
MIN(CASE WHEN event_name = 'purchase' THEN event_time END)對於 visit(09:00), purchase(09:05), signup(09:10), purchase(09:20)，獨立最小值會選到 09:05 的購買，可能錯誤地判定使用者未完成漏斗。依賴式查詢會先選 09:10 的註冊，再選 09:20 的購買。 只有每個最小值都受上一步實際選中事件約束時，條件最小值才可能正確，這正是本題的核心難點。
可以從兩個索引開始評估：
CREATE INDEX events_anchor_scan_idx
ON events (event_name, event_time, user_id, event_id);
CREATE INDEX events_user_step_lookup_idx
ON events (user_id, event_name, event_time, event_id);第一個索引對應群組建構時的事件名稱和時間範圍，第二個對應反覆執行的使用者級步驟查找。索引會增加 儲存和寫入放大，實際收益取決於選擇性、資料聚集、表大小和 PostgreSQL 最終計畫，應使用代表性資料 執行 EXPLAIN (ANALYZE, BUFFERS)。對高頻大規模報表，可以考慮帶穩定序列欄位的事件模型或增量 維護的使用者旅程表，但必須保留同一群組與視窗口徑。
一組緊湊的對抗性資料應涵蓋：
1. purchase 在 visit 前 -> 只算造訪
2. visit、purchase、signup -> 算註冊，不算購買
3. visit、purchase、signup、後續 purchase -> 完成三步
4. 重複 signup 和 purchase -> 每一步仍只計一位使用者
5. purchase 恰好在 visit + 24 小時 -> 計入
6. purchase 晚於 visit + 24 小時一微秒 -> 不計入
7. 時間戳相同 -> 由 event_id 決定順序
8. 區間內多次 visit -> 仍以最早一次為錨點
9. 沒有 visit -> 計數為 0，比例為 null還要測試報告生命週期。若 end_at 是 7 月 1 日零點，6 月 30 日 23:59 的造訪最晚可在 7 月 1 日 23:59 轉換；7 月 1 日零點執行報表還不完整。成熟度應依賴攝取水位，不能只看目前時間；延遲事件到達 後，要重算受影響群組。
高品質範例回答
「寫 SQL 前，我會先確定歸因規則：每位使用者只有一次漏斗機會，以報告區間內最早造訪為錨點。報告 區間是半開的，後續步驟可以晚於區間結束，但註冊和購買都必須落在錨點後 24 小時內。同一時間戳依賴 題目保證的事件 ID 順序。
我先篩選區間內造訪，按每位使用者的 (eventtime, eventid) 排序，只保留第一筆，得到正確分母 粒度。對每個錨點，我用左側橫向子查詢按同一元組排序，取最早有效註冊；第二個左側橫向子查詢引用 這次註冊，取最早有效購買，同時讓截止點繼續綁定造訪。兩個查找都有 LIMIT 1，所以進度表仍是一列 一位造訪使用者；沒有到達的步驟保留 null，不會把使用者刪掉。
最終聚合統計所有進度列，再分別統計註冊和購買時間非空的列。兩個比例都除以造訪群組人數，並用 NULLIF 處理空輸入。我會斷言 購買人數 <= 註冊人數 <= 造訪人數，逐階段抽查事件鏈，並重點 測試倒序事件、重複步驟、相同時間戳和 24 小時邊界。
生產環境中，只有攝取水位覆蓋完整機會視窗後，我才會把最新群組標為最終結果。我會比較支援錨點掃描 和使用者級步驟查找的索引前後計畫。如果時間戳和事件 ID 不能可信表達順序，我會先修復事件契約， 因為查詢無法恢復缺失的因果資訊。」
常見錯誤
- 只檢查事件是否存在。 三個事件標記可能把
purchase → visit → signup也算成轉換；漏斗必須
建構明確的有序鏈。
- 分別取每一步的首次時間。 最早購買可能在被選中註冊前，即使後面另有一次購買完成有效路徑。
- 無意中讓每次造訪都成為錨點。 粒度會從一次使用者機會變成一次造訪機會，計數與歸因都會改變。
- 只用
event_time排序。 同一時間戳會導致結果不穩定；需要可信的序列鍵，否則應承認順序未知。 - 每一步都重啟視窗。 註冊給 24 小時、購買再給 24 小時，不符合以錨點為起點的 24 小時口徑。
- 用
end_at篩選後續步驟。 會縮短靠近報告邊界使用者的機會，應讀取到每個錨點自己的截止時間。 - 使用內連接橫向查詢。 沒有註冊的造訪使用者會消失，從而誇大轉換率。
- 統計原始事件列。 重試和重複操作可能讓步驟人數大於群組人數。
- 誤用前一步作為分母。 這算的是步驟間轉換率，不是題目要求的造訪累計轉換率。
- 發布未成熟群組。 完整視窗被可靠資料覆蓋之前，缺少後續事件不能證明使用者已經流失。
延伸問題與回答
如果任意一次造訪都可以開啟有效旅程呢？
粒度需要改變。為每次造訪產生候選錨點，分別配對有效鏈，再按「每位使用者最早完成的旅程」等規則 歸因。不能只替換群組 CTE：重疊視窗可能爭用同一個後續事件，產品口徑還需明確事件能否重複使用。
如果每一步必須屬於同一工作階段或商品呢？
從錨點攜帶關聯鍵，並在兩個橫向查找中都要求該鍵一致。粒度會變成使用者—工作階段或使用者—商品。 使用前還要定義空鍵如何處理，以及關聯鍵是否不可變。
十步漏斗怎麼實作？
手寫十個橫向連接很難審查。可以根據資料庫能力選擇遞迴 SQL、原生序列或漏斗函式，或用狀態機式 預處理走訪有序事件。無論實作如何，都要保留同一組不變量：明確錨點、步驟單調有序、共用截止時間和 可審計的歸因規則。
事件延遲或亂序怎麼辦？
區分事件時間與攝取時間，發布完整性水位，並重算視窗內收到延遲資料的群組。事件到達後仍可按事件時間 排序，但在約定的延遲容忍期結束前，報表只能是暫定結果。
能否完全用視窗函式解決？
可以，例如掃描每位使用者的有序事件流並攜帶狀態；但單獨使用 LAG 不夠，因為步驟之間可能夾雜 無關事件和重複事件。橫向查詢能直接表達「下一步依賴已選中的上一步」。只有另一種寫法的狀態轉移和 歸因同樣清楚，且實測計畫更好時，才值得替換。