プロンプトと適用されるコンテキスト
次の2つの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日までのユーザーについて、登録日および獲得チャネルごとにグループ化した正確なDay-7リテンションをレポートしてください。ビジネスタイムゾーンはAmerica/New_Yorkです。Day 0はユーザーのローカル登録日です。ユーザーがローカルカレンダー日signup_day + 7に少なくとも1つのcore_action_completedイベントを実行した場合、そのユーザーはDay 7にリテイン（維持）されたとみなされます。Day 1〜6、Day 8、またはそれ以降の日付のイベントはこの定義を満たしません。
cohort_day、acquisition_channel、cohort_size、retained_users、およびd7_retention_rateを返してください。条件を満たす複数のイベントは、ユーザーごとに1回としてカウントします。リターンイベントのないユーザーも分母に残す必要があります。NULLのチャネルはunknownとしてグループ化します。
data_complete_through = '2026-02-01 05:00:00+00'と仮定します。これは排他的な取り込みウォーターマークであり、ニューヨークにおける2月1日の開始時の深夜0時に相当します。Day-7のカレンダー日全体がそのウォーターマークより前にある場合にのみ、登録コホートを含めてください。1月24日のコホートはDay 7が1月31日であるため成熟していますが、1月25日のコホートは成熟していません。
この問題は、アナリティクスエンジニア、データアナリスト、プロダクトアナリティクスの面接で有用です。難しいのは除算ではなくメトリクス契約だからです。クエリは、集計を行う前にコホートの粒度、リターンウィンドウ、タイムゾーン、データの完全性、およびセグメンテーションを整合させる必要があります。
面接官が評価するポイント
最初のシグナルは、候補者が「Day-7リテンション」の意味を質問するかどうかです。似ているように聞こえる3つのメトリクスは実質的に異なります。すなわち、正確にDay 7当日のアクティビティ、Day 1〜7の間の任意の時点でのアクティビティ、そしてDay 7以降のアクティビティです。クエリが構文的に完璧であっても、誤った質問に答えている可能性があります。本プロンプトは1つ目の定義を要求しています。
2番目のシグナルは、分母の規律です。分母は、成熟した登録コホート内のすべての対象ユーザーです。eventsテーブルから開始したり、リターンを登録に内部結合（inner join）したりすると、一度も戻らなかったユーザーが暗黙的に除外され、リテンションが過大評価されます。コホートを最初に構築し、外部結合（left join）で保持する必要があります。
3番目のシグナルは、粒度の制御です。出力は登録日とチャネルごとに1行ですが、リテンションフラグはユーザーごとに最大1行です。生のイベントデータには、リトライ、重複、および同日の複数の有効なアクションが含まれる場合があります。イベント行をカウントすると、リテインされたユーザーではなくアクション数が測定されてしまうため、分子はユーザーレベルで重複排除する必要があります。
4番目のシグナルは、時間的な正確性です。ローカルのカレンダー日は常に固定の24時間間隔であるとは限らず、名前付きタイムゾーンは夏時間（DST）のルールによってUTCオフセットが変化する可能性があります。最初にローカル日付を導出し、名前付きタイムゾーンの深夜0時から各ユーザーの対象日の境界を構築することで、契約を直接表現できます。event_at = signup_at + INTERVAL '7 days'のようなテストは、代わりに経過時間の質問に答えてしまいます。
最後のシグナルは、運用上の判断力です。直近のコホートには完全な観測ウィンドウが必要ですが、壁時計（実時間）はイベントパイプラインの完了を証明しません。候補者はデータウォーターマークを使用し、遅延到着イベントについて説明し、信頼できる最小のレポーティング粒度を検証し、クエリ頻度とデータ量に応じてインデックスまたは日次アクティビティテーブルを選択する必要があります。
回答前に確認すべき質問
- Day 7は正確（exact）ですか、期間内（bounded）ですか、それともローリング（rolling）ですか？ この回答では、登録後の7番目のローカル日付におけるイベントを厳密に使用します。「7日以内」や「Day 7以降」には異なる述語が必要です。
- どのイベントがリテンションを証明しますか？ プロンプトでは
core_action_completedを使用しています。ログイン、ページビュー、購入、またはその他のイベントは異なるプロダクトの意味を持つため、安易に置き換えるべきではありません。 - 何が「日」を定義しますか？ レポーティング契約では
America/New_Yorkのカレンダー日を使用します。ユーザーごとのタイムゾーンやUTCを使用すると、コホートの所属やリターンウィンドウが変化します。 - コホートはいつ成熟しますか？ コホートは、排他的なデータウォーターマークがそのコホートのDay 7の次のローカル深夜0時以降にある場合にのみレポート可能です。
- 遅延イベントはどのように処理されますか？ ウォーターマークが後で進んだり、ウォーターマークより前にバックフィルされたイベントが到着したりした場合、影響を受けるコホートを再計算する必要があります。表示されるレートは不変のものとして扱うべきではありません。
- どのチャネル値が適用されますか？ クエリは、
users.acquisition_channelが登録時の不変のアトリビューションであると想定しています。変更可能な現在のチャネルの場合は、バージョニングされたアトリビューションスナップショットが必要です。 - コホートの粒度は何ですか？ このプロンプトでは登録日とチャネルでグループ化します。週次コホートは同じユーザーレベルのロジックを使用しますが、最終的なグループ化キーが異なります。
- レートは小数ですか、それともパーセンテージですか？ クエリは小数第4位に四捨五入された小数を返します。
0.3333は33.33%を意味します。
30秒の回答フレームワーク
「合意されたビジネスタイムゾーンにおけるユーザーの登録カレンダー日プラス7日として正確なDay 7を定義します。まず、ローカル深夜0時のUTC境界から1月のコホートを構築し、登録日と登録時チャネルを持つ行をユーザーごとに1行保持します。次に、完全な対象日が排他的データウォーターマークを超えているコホートを除外します。残りの各ユーザーについて、ローカルDay-7の半開区間内で対象イベントを検索し、ユーザーごとにリテインされた行を1行に重複排除し、そのフラグを成熟コホートにleft joinします。最後に、日付とチャネルごとにコホートユーザーとリテインされたユーザーをカウントします。重複、リターンゼロ、Day 6およびDay 8のイベント、ローカル深夜0時、夏時間の境界、および成熟度のカットオフをテストします。」
ステップバイステップの詳細解説
レポーティング契約を明確にするパラメータから始めます。cohort_endは排他的であり、ウォーターマークは最初の未観測の瞬間です。半開区間により、深夜0時のイベントの二重カウントを防ぎ、隣接する日付間でクリーンに構成できます。
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;コホートフィルタは、インデックス付きのtimestamptz値と比較する前に、1月のローカル深夜0時の境界をUTC日時に変換します。これは、WHERE句内のすべてのsignup_at行に日付変換を適用するよりも優れています。ローカル日付のルールを明示しながら、タイムスタンプ列で通常のレンジスキャンを適用可能に保ちます。
成熟度は日付で考える方が容易です。ウォーターマークはローカル日付の2月1日に変換されます。対象日は2月1日より厳密に前である必要があり、これはその終了の深夜0時がカバーされていることを意味します。1月24日プラス7は1月31日となりパスします。1月25日プラス7は2月1日となり失敗します。ウォーターマークがローカルの深夜0時ではなく正午である場合、堅牢な形式では対象日の終了日時をウォーターマークと直接比較します：
((c.signup_day + 8)::timestamp AT TIME ZONE p.tz)
<= p.data_complete_throughretained CTEは、semi-joinのようなユーザーフラグを作成します。DISTINCTにより、イベントプロデューサーがリトライした場合や、ユーザーがコアカスタムアクションを10回完了した場合でも、各ユーザーの寄与は最大1行になります。ユーザーは正確に1つのコホート行に属するため、提示されたスキーマではuser_idによる結合で十分です。同じ人物が複数の登録エピソードを持つ可能性がある場合、モデルには安定したエピソード識別子が必要となり、結合にはそれを使用します。
最後のleft joinは、成熟したすべてのコホートメンバーを保持します。したがって、COUNT(*)は分母を測定し、COUNT(r.user_id)は一致したリテインフラグのみをカウントします。NULLIFはグループレベルで防御的ですが、mature_cohortから生成されたグループには必然的に少なくとも1行が含まれます。
小規模なテストデータで考えてみます。1月1日のオーガニックコホートに3人のユーザーがいて、1人だけが条件を満たすDay-7アクションを持っているとします。その結果は3、1、0.3333になります。1月2日の有料コホートに2人のユーザーがいて両方がリターンした場合、結果は2、2、1.0000になります。1月25日の登録は、提供されたウォーターマークにおいてそのコホートが未成熟であるため、その後のイベントに関係なく除外されます。
表示されたグループレートを平均して全体合計を作成しないでください。1人の日次コホートに1,000人のコホートと同じ重みを与えてはなりません。正しいロールアップは次のとおりです：
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';部分イベントインデックスは、このイベント定義が安定しており、書き込みおよびストレージのコストを正当化できるほど重要な場合にのみ適切です。非常に大規模なイベントストリームに対する定期的なダッシュボードの場合、(user_id, activity_day, event_name)のようなユニークキーを持つ増分更新テーブルを使用することで、生のイベントスキャンの繰り返しを回避できます。そのactivity_dayは同じ名前付きタイムゾーン契約の下で導出される必要があります。そうでない場合、最適化によってメトリクスが変化してしまいます。
Uを対象ユーザー数、Eを結合を通じて調査された関連イベント行数とします。コホートスキャンは選択されたユーザー数に対して線形であり、重複排除は通常、リテイン候補に対するハッシュまたはソートであり、最終的な集計は成熟ユーザー数に対して線形です。実際のコストはイベントの選択性、インデックス、統計情報、およびプランの形状に依存するため、SQLテキストのみから一律の計算量を約束するのではなく、代表的なデータでEXPLAIN (ANALYZE, BUFFERS)を確認してください。
質の高い模範解答
「SQLを書く前に、4つの定義を確定させます。正確なDay 7、リターンイベントとしてのcore_action_completed、ニューヨークのカレンダー日、および排他的な完全性ウォーターマークです。分母は、Day 7の全日程が観測されたすべての1月の登録です。分子は、その集合の中で少なくとも1つの対象イベントを持つ個別ユーザーです。
まずコホートを構築します。signup_atの範囲フィルタのために1月のローカル開始・終了の深夜0時をUTC日時に変換し、各ユーザーのローカル登録日と登録時チャネルを保持します。成熟度は監査できるように個別にフィルタリングします。2月1日ローカル深夜0時のウォーターマークの場合、含まれる最後の登録日は1月24日です。
成熟した各ユーザーについて、user_id、対象イベント名、およびsignup_day + 7のローカル深夜0時からsignup_day + 8のローカル深夜0時までの半開区間でイベントを結合します。これらの境界は名前付きタイムゾーンで計算されるため、夏時間の移行によってカレンダーのルールが168時間ルールに変わることはありません。個別ユーザーIDを選択し、リテインフラグをコホートにleft joinし、登録日とチャネルで集計します。リターンのないユーザーも分母に残る必要があるため、このleft joinは不可欠です。
各CTEを個別に検証します。cohort CTEは1月の登録ごとに1行を持つ必要があります。maturity CTEはこのウォーターマークに対して1月24日で停止する必要があります。retained CTEはユニークユーザーを持つ必要があります。そして最終カウントは0 <= retained_users <= cohort_sizeを満たす必要があります。テストケースでは、重複イベント、リターンなし、誤ったイベント名、Day 6とDay 8、ローカル深夜0時の両側、夏時間の日付、NULLチャネル、遅延イベントのバックフィルをカバーします。グループ全体の合計を求める場合は、レートを平均するのではなく、リテインされたユーザーの合計をコホートユーザーの合計で除算します。」
よくある間違い
- 内部結合（inner join）を使用する。 これによりリターンゼロのユーザーが破棄され、レートが吊り上がります。最初にコホートを構築し、ユーザーレベルのリテインフラグをleft joinしてください。
- ユーザーではなくイベントをカウントする。
COUNT(e.event_id)がコホートサイズを超える可能性があります。分子をユーザーごとに重複排除するか、ブール値の存在確認を使用してください。 - Day 7を曖昧なままにする。 Day 1〜7またはDay 7以降をカバーする述語は、異なるメトリクスを計算します。SQLの前に区間の定義を言葉で記述してください。
- 登録タイムスタンプ＋168時間と比較する。 それは経過時間のリテンションであり、名前付きタイムゾーンのカレンダー日定義ではないため、夏時間の変更前後で差異が生じる可能性があります。
- フィルタ内でインデックス付きタイムスタンプをキャストする。 すべての
signup_atを日付に変換すると、有用なレンジスキャンが妨げられる可能性があります。代わりに、定数のローカル境界を日時に変換してください。 - 未成熟なコホートを含める。 最近のユーザーにはリターンする十分な機会がまだ与えられておらず、人為的なパフォーマンス低下が生じます。現在時刻だけでなく、パイプラインのウォーターマークでゲートしてください。
- ウォーターマークを永続的な真実として扱う。 バックフィルによって、すでにレポートされたコホートが変更される可能性があります。再計算と鮮度の動作を定義してください。
- 変更可能なチャネル値を読み取る。 現在のアトリビューションは、過去のコホートに将来の情報を漏洩させる可能性があります。不変の登録フィールドまたはバージョニングされたスナップショットを使用してください。
- グループのパーセンテージを平均する。 コホートサイズが異なる場合、重み付けされていない平均は合計を歪めます。最初にカウントをロールアップしてください。
- 空値およびNULLのセマンティクスを無視する。 NULLチャネルを意図的にマップし、メトリクスを公開する前にイベントデータの欠落がアクティビティゼロを意味するのか、パイプラインの不完全性を意味するのかを判断してください。
フォローアップの質問と回答
Day 1〜7以内のリテンションはどのように計算しますか？
同じコホートと成熟度のアプローチを維持しますが、イベント間隔を変更して、signup_day + 1のローカル深夜0時から開始し、signup_day + 8のローカル深夜0時より前に終了するようにします。成熟度には依然として7日目全体が完了している必要があります。Day 0をカウントするかどうかを明記してください。これについてはプロダクトツールやチームによって異なります。
「Day 7以降」のリテンションはどのように計算しますか？
下限はsignup_day + 7のローカル深夜0時のままですが、上限はレポーティングのカットオフになります。そのメトリクスは累積的であり、観測期間の長さに依存します。古いコホートほどリターンする機会が多くなります。共通の経過日数でのみコホートを比較するか、上限のない単一の数値ではなくリテンション曲線を公開してください。
PostgreSQLは個別のretained CTEなしでこれを集計できますか？
はい。lateral EXISTSルックアップまたは慎重に構築された集計により、コホートユーザーごとに1つのブール値を返すことができます。直接結合にCOUNT(DISTINCT e.user_id) FILTER (WHERE ...)を加える方法も可能ですが、集計前に多数のイベント行が実体化される可能性があります。個別のCTEにより粒度と正確性の証明を監査しやすくなります。測定されたデータから最終的なプランを選択してください。
各ユーザーが異なるレポーティングタイムゾーンを持っている場合はどうなりますか？
登録エピソードに適用されるゾーンを保存し、その同じ値からコホート日と対象境界の両方を導出します。過去のタイムゾーン変更には定められたポリシーが必要です。また、ユーザーごとのタイムゾーンは、1つのカレンダーコホートラベルが異なるUTC間隔にまたがることを意味するため、事前集計では適用可能なタイムゾーンまたは正規化済みのローカル日を保持する必要があります。
成熟度の境界はどのようにテストしますか？
指定されたウォーターマークの下で1月24日と1月25日の登録を作成します。両方に対象日の有効なイベントを与えます。1月24日は表示され、1月25日は表示されてはなりません。また、対象日の終了深夜0時の1秒前と正確にその時点のウォーターマークをテストして、排他境界のルールを確認します。
遅延到着イベントはどのように処理すべきですか？
イベント時間のウォーターマークを公開し、対象イベント間隔がバックフィルと重複するすべてのコホートを再計算します。取り込みの遅延に既知のサービスレベルがある場合、レポートは公称のDay 7終了を超えて安全遅延（safety delay）を追加できます。生カウントとメトリクスバージョンを保持して、修正を追跡可能にします。
本番環境ではどのような不変条件（invariant）を監視しますか？
すべてのグループが正のcohort_sizeを持ち、リテインされたユーザー数が0からコホートサイズの間に収まり、未成熟なコホート日が存在せず、コホートの合計が登録ソースと一致し、最大イベント時間が宣言されたウォーターマークをカバーしていることを確認します。パイプラインのギャップがリテンションの低下と誤認されないように、イベント量や取り込み遅延の異常に対して個別にアラートを設定します。