プロンプトと適用されるコンテキスト
次のPostgreSQLテーブルが与えられます：
CREATE TABLE events (
user_id bigint NOT NULL,
event_id bigint PRIMARY KEY,
event_name text NOT NULL,
event_time timestamptz NOT NULL
);3ステップのファネル（visit → signup → purchase）を計算してください。ユーザーは、半開集計期間 [start_at, end_at) 内に 少なくとも1つの visit がある場合にコホートに入ります。その期間内の 最も早い訪問がファネルのアンカーとなります。対象となる登録は、そのアンカーの後の最も早い登録です。 対象となる購入は、選択された登録の後の最も早い購入です。両方ともアンカー訪問から 24時間以内に発生する必要があります。
event_id は一意です。イベントプロデューサーは、同一ユーザーの2つのイベントがタイムスタンプを 共有している場合、より小さい event_id の方が先に発生したことを保証します。アンカーからちょうど24時間後の購入は カウントされますが、1マイクロ秒でも遅いものはカウントされません。ファネルステップ間のイベントは許可され、重複したステップ イベントによって同一ユーザーが二重にカウントされてはなりません。
visited_users、signed_up_users、purchased_users、signup_rate、および purchase_rate を持つ1行を 返してください。両方の率は訪問コホートからの累積であり、小数第4位に四捨五入された 小数として表されます。空のコホートの場合は、カウントを0、率をnullとして返します。
この問題は、データアナリスト、アナリティクスエンジニア、およびプロダクトアナリティクスの面接に適しています。これは 条件付き集約をはるかに超える内容をテストします。候補者はコホートの粒度を定義し、イベントの順序を維持し、 イベントが繰り返される際に有効なチェーンを選択し、時間境界を処理し、クエリがあり得ないジャーニーをカウント できないことを証明しなければなりません。
面接官が評価するポイント
最初のシグナルは、メトリクス定義（規約）への規律です。「3つのイベントすべてを完了したユーザー」だけでは不十分です。 面接官は、どの訪問がジャーニーのアンカーとなるのか、ステップが順序付けられている必要があるか、 ウィンドウが訪問の24時間後に閉じるのか各ステップの後に閉じるのか、そして率の分母として前のステップを使用するのか 元のコホートを使用するのかを知りたがっています。それぞれの選択によって結果が変わります。
2番目のシグナルは粒度の制御です。元のテーブルは1イベントあたり1行ですが、出力ではユーザー数をカウントします。 コホートはユーザーごとに最大1行でなければならず、その後のすべてのルックアップはそのコホート行に対して最大1つのイベントを 生成しなければなりません。大雑把な結合の後に COUNT(DISTINCT user_id) を適用すると、正しいイベントチェーンを 確立するのではなく、誤ったチェーンを隠してしまう可能性があります。
3番目のシグナルは順次推論です。 MIN(CASE WHEN event_name = 'purchase' ...) のような独立した式は、必ずしも選択された登録の後の 最も早い購入を見つけるとは限りません。ユーザーは購入し、その後登録し、さらに再度購入する可能性があります。有効な購入は 2回目の購入です。したがって、各ステップには前のステップで選択されたイベントが必要です。
4番目のシグナルは決定論的な時間ロジックです。タイムスタンプだけでは同時刻のイベントを順序付けることはできません。 このプロンプトはタイブレークとして event_id を提供しているため、比較キーはタプル (event_time, event_id) になります。その保証がなければ、データから同時刻のイベントのどちらが先に発生したかを 証明することはできず、SQLが因果関係を勝手に作り出すべきではありません。
最後のシグナルは運用上の判断力です。ウィンドウが end_at を超えて伸びるファネルには、その後の イベントデータが必要です。レポートが確定するのは、パイプラインが end_at + 24 hours まで処理を完了した後のみです。 候補者は、構文的に有効なSQLにとどまらず、遅延データ、インデックス、クエリプラン、およびメトリクス定義を検証するテスト フィクスチャについて議論する必要があります。
回答前に確認すべき質問
- どのイベントがユーザーのアンカーになりますか？ この回答では、ユーザーが期間前に訪問していたとしても、
集計期間内の最も早い訪問を使用します。「過去すべての最初の訪問」とする場合は、履歴データと別のコホートフィルタが必要になります。
- ファネルは順序付けられていますか？ はい。登録はアンカー訪問の後でなければならず、購入は選択された登録の後でなければ
なりません。順不同のイベント存在確認は別のメトリクスです。
- ウィンドウの開始と終了はどこですか？ アンカー訪問から開始し、
visit_time + 24 hoursで包含的に
終了します。登録後に再スタートすることはありません。
- 以降のステップは
end_atの外に出てもよいですか？ はい。ユーザーの24時間ウィンドウ内であれば問題ありません。
end_at はアンカー訪問を選択するものであり、コンバージョンの機会を切り捨てるものではありません。
- 同じタイムスタンプはどのように順序付けられますか？ プロデューサーの保証に基づき、まず
event_timeを比較し、
次に event_id を比較します。信頼できる順序が存在しない場合、同時刻のステップは曖昧になります。
- 各率を定義する分母は何ですか？ 両方とも
visited_usersを使用します。ステップ間の購入率は
購入者数を登録ユーザー数で割ることになり、別の列名にする必要があります。
- 空の入力は何を返しますか？ カウントは0、意味のある分母が存在しないため率はnullになります。
NULLIF はゼロ除算を防ぎます。
- レポートはいつ確定しますか？ データ完全性ウォーターマークがすべてのコホートメンバーの完全な24時間の
コンバージョン機会をカバーし、遅延到着ポリシーが適用された後でのみ確定します。
30秒の回答フレームワーク
「まず、半開集計期間内の訪問をランク付けしてランク1を保持することで、コホートをユーザーあたり1行にします。 各アンカーに対して、LEFT LATERALルックアップにより、(event_time, event_id) が訪問後であり、かつ時間が24時間以内である 最も早い登録を見つけます。2つ目のLEFT LATERALルックアップは購入に対して同じことを行いますが、元の24時間の 期限を保持したまま、選択された登録の後に開始します。これにより、訪問ユーザーごとに1つの進行状況行が作成されます。 その後、到達した2つのステージに対してフィルタリングされたカウントを使用し、両方を訪問数で割ります。イベントの逆順、 重複、同一タイムスタンプ、厳密な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;メインクエリでは、2つの依存するLATERALルックアップを使用します。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時間ウィンドウ内に 含まれることの3つのプロパティを同時に証明します。順序付けと LIMIT 1 により、決定論的な1つのイベントが選択されます。 購入のルックアップはその選択された登録に依存するため、それ以前の購入は無視され、その後の有効な購入は 対象として残ります。その期限は依然として c.visit_time を参照します。そうでなければ、クエリは誤って最大48時間を 許可してしまいます。
この貪欲法的な選択は、固定された3ステップの順序付きファネルに対して正しく機能します。最も早い条件合致の 登録を選択しても、より遅い登録が許可し得たはずの購入を排除することはありません。より遅い登録の後の購入は 必ずより早い登録の後でもあり、共通の期限は変わらないためです。同じ議論が最も早い条件合致の購入の 選択にも当てはまります。各ルックアップ後の不変条件は、選択されたチェーンが有効であり、ウィンドウの残りの 可能な限り最大のサフィックスを残していることです。
各LATERALルックアップは最大1行を返すため、progress CTEはコホートユーザーあたり正確に1行を持ちます。 したがって、フィルタリングされた集計は DISTINCT を必要とせずにユーザー数をカウントできます。購入数が 登録数を超えることはできず、登録数が訪問数を超えることはできません。これらの不等式は、結果レベルの 有用なアサーションとなります。
魅力的に見える「独立最小値」パターンは避けてください：
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の購入を選択します。条件付き最小値は、各最小値が前に選択されたステップによって制約されている場合にのみ 機能しますが、それこそがここでの中心的な難しさです。
手始めとして考えられるインデックスは2つあります：
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);1つ目は、コホートの構築に使用されるイベント名と時間の範囲をサポートします。2つ目は、ユーザーごとの 繰り返しのステップ探索をサポートします。これらはストレージと書き込みのオーバーヘッドを増加させ、実際の有効性は 選択性、クラスタリング、テーブルサイズ、およびPostgreSQLが選択したプランに依存します。代表的なデータに対して EXPLAIN (ANALYZE, BUFFERS) で検証してください。頻繁に実行される大規模なレポートの場合は、安定したシーケンスフィールドを持つ イベントモデルや、段階的に維持されるユーザージャーニーテーブルの方が適している場合がありますが、同じコホートおよび ウィンドウの規約を維持する必要があります。
コンパクトな敵対的テストフィクスチャには、次のケースを含める必要があります：
1. purchase before visit -> visitor only
2. visit, purchase, signup -> signed up, not purchased
3. visit, purchase, signup, later purchase -> completes all steps
4. duplicate signups and purchases -> user still counts once per stage
5. purchase exactly at visit + 24 hours -> counts
6. purchase one microsecond after visit + 24 hours -> does not count
7. equal timestamps -> event_id determines sequence
8. repeated visits in the interval -> earliest visit remains the anchor
9. no visits -> zero counts and null ratesまた、レポートのライフサイクルもテストしてください。end_at が7月1日の午前0時である場合、6月30日23:59の 訪問者は7月1日23:59までコンバージョンする可能性があります。7月1日午前0時の実行では不完全です。実時間ではなく 取り込みウォーターマークを使用し、遅延イベントが到着した際には影響を受けるコホートを再計算してください。
質の高い模範解答
「SQLを書く前にアトリビューションルールを明記します。集計期間内の最も早い訪問をアンカーとする、ユーザーあたり 1回のファネル機会です。期間は半開区間であり、以降のステップはその終了後に発生してもよく、登録と購入の両方が アンカーから24時間以内に収まる必要があります。同一タイムスタンプは、保証されたイベントIDの順序によって順序付けられます。
まず訪問を期間でフィルタリングし、ユーザーごとに (event_time, event_id) でランク付けして、ランク1を 保持します。これにより正しい分母の粒度が得られます。各アンカーに対して、同じタプルで順序付けられたLEFT LATERALサブクエリを 使用して、最も早い有効な登録を取得します。2つ目のLEFT LATERALサブクエリは選択された登録を参照し、訪問に紐づく 期限を維持しながら、最も早い有効な購入を取得します。各ルックアップには LIMIT 1 があるため、進行状況リレーションは 訪問者あたり1行のままです。欠落しているステップは、ユーザーを削除するのではなくnullのままになります。
最終的な集約では、すべての進行状況行をカウントし、非nullの登録および購入タイムスタンプに対してフィルタリングされた カウントを使用します。両方の率は訪問コホート数で除算し、空の入力には NULLIF を使用します。 purchased_users <= signed_up_users <= visited_users をアサートし、すべてのステージのサンプルチェーンを検査し、 特に逆順イベント、重複ステップ、同一タイムスタンプ、および24時間境界の両側をテストします。
本番環境では、取り込みウォーターマークが完全な機会ウィンドウをカバーするまで、最新のコホートを完全とは見なしません。 アンカースキャンとユーザーごとのステップルックアップをサポートするインデックスの有無でプランを比較します。 タイムスタンプとイベントIDが信頼できる順序を表していない場合は、クエリでは失われた因果関係を復元できないため、 作業を中断してイベント規約を修正します。」
よくある間違い
- イベントの存在のみをチェックする。 3つのイベントフラグでは
purchase → visit → signupをカウントしてしまう可能性があります。
ファネルには明示的な順序付きチェーンが必要です。
- 独立した最初のタイムスタンプを取得する。 より遅い購入が有効なジャーニーを完了する場合であっても、最も早い
購入が選択された登録より前にある可能性があります。
- 意図せずすべての訪問をアンカーにしてしまう。 これにより粒度が1ユーザー機会から1訪問機会に変更され、
ユーザーのカウントやアトリビューションが異なってしまう可能性があります。
- 順序付けに
event_timeのみを使用する。 同一タイムスタンプにより結果が不安定になります。信頼できる
シーケンスキーを使用するか、順序が不明であることを認識してください。
- 各ステップでウィンドウを再スタートする。 登録に24時間、購入にさらに24時間を与えることは、
アンカーベースの24時間規約に違反します。
- 以降のステップを
end_atでフィルタリングする。 これにより、集計境界付近の訪問者の機会が
短縮されてしまいます。各アンカーの期限まで以降のイベントを取得してください。
- INNER LATERAL JOINを使用する。 登録のない訪問者が除外され、コンバージョン率がつり上がります。
- 生のイベント行をカウントする。 リトライや重複アクションにより、ステージのカウントがコホートサイズを超える可能性があります。
- 誤って前のステップで除算する。 これにより、訪問からの明記された累積率ではなく、ステップコンバージョン率が計算されてしまいます。
- 未成熟なコホートを公開する。 信頼できるデータによってウィンドウ全体がカバーされるまで、以降のイベントが存在しないことは
離脱の証拠にはなりません。
フォローアップの質問と回答
どの訪問でも有効なジャーニーを開始できる場合はどうなりますか？
粒度が変わります。すべての訪問に対して候補となるアンカーを生成し、それぞれから有効なチェーンをマッチングし、 ユーザーごとに最も早く完了したジャーニーなどのアトリビューションルールを適用します。単にアンカーCTEを 置き換えるだけではいけません。重複するウィンドウが同じ後続イベントを取り合う可能性があるため、プロダクトの規約で 再利用が許可されているかどうかを定義する必要があります。
各ステップがセッションまたは製品を共有する必要がある場合はどうなりますか？
アンカーから関連付けキーを引き継ぎ、両方のLATERALルックアップでそれを必須とします。粒度はユーザー単体ではなく、 ユーザー×セッションまたはユーザー×製品になります。結合に依存する前に、nullキーの動作やキーがイミュータブルかどうかを 定義してください。
10ステップのファネルをどのようにサポートしますか？
10個の手書きのLATERAL結合は監査が困難です。データベースに応じて、再帰的SQL、ネイティブのシーケンスまたは ファネル関数、あるいは順序付きイベントをステートマシンとしてトラバースする前処理ジョブを使用します。1つの定義された アンカー、単調なステップ順序、1つの共有期限、および監査可能なアトリビューションルールという同じ不変条件を維持します。
イベントが遅れて到着したり、順不同で到着した場合はどうなりますか？
イベント時間と取り込み時間を分離し、完全性ウォーターマークを公開し、ウィンドウに遅延データを受信したコホートを 再集計します。イベント時間の順序は到着後も計算可能ですが、合意された遅延インターバルが経過するまでレポートは暫定的なものとなります。
ウィンドウ関数で問題全体を解決できますか？
各ユーザーの順序付きストリームをスキャンして状態を引き継ぐことなどで解決は可能ですが、ステップ間に無関係な イベントや重複イベントが存在する可能性があるため、LAG だけでは不十分です。LATERALを使用した ソリューションは、選択されたステップ間の依存関係を明示的にします。その状態遷移とアトリビューションセマンティクスが 同等に明確であり、測定されたプランが優れている場合にのみ、別の記述方法を選択してください。